How to insert rows which optionally have parent rows in the same table at once

Given is the following table:

CREATE TABLE public.parenttest (
  id      bigserial NOT NULL PRIMARY KEY,
  mydata  varchar(30),
  parent  bigint
  ) WITH (
    OIDS = FALSE
); 

I'd like to insert a bunch of rows. Some of these rows should take the sequence-generated id of a row inserted before as value for the column parent.

For example:

INSERT INTO parenttest (mydata,parent) VALUES ('rootnode',null); 
INSERT INTO parenttest (mydata,parent) VALUES ('child1', /*id of rootnode*/); 
INSERT INTO parenttest (mydata,parent) VALUES ('child2', /*id of rootnode*/); 
INSERT INTO parenttest (mydata,parent) VALUES ('child2.1', /*id of child 2*/);
INSERT INTO parenttest (mydata.parent) VALUES ('child2.2', /*id of child 2*/);

...should result in the following datasets (id, mydata,parent)

1,'rootnode',null
2,'child1',1
3,'child2',1
4,'child2.1',3
5,'child2.2',3

UNTIL child2.2, everything is fine when i'm using

SELECT currval('parenttest_id_seq');

to get the parent's id but then i'm getting the id of child2.2, of course.

It's important to me that i can do all necessary stuff with as few client-side requests as possible - and i'd like to do all id generation on the server-side.

Answers


Here's how I'd do it, if it's acceptable for the rows to briefly have a NULL parent:

INSERT INTO parenttest (mydata) VALUES
('rootnode'), 
('child1'),
('child2'), 
('child2.1'),
('child2.2');

UPDATE parenttest SET parent = (select id from parenttest pt where
(pt.mydata = 'rootnode' and parenttest.mydata in ('child1','child2')) or
(pt.mydata = 'child2' and parenttest.mydata in ('child2.1','child2.2')))
WHERE
  mydata in ('child1',
             'child2', 
             'child2.1',
             'child2.2');

fiddle


You could do something as ugly as:

INSERT INTO parenttest (mydata,parent) VALUES ('rootnode',null); 
INSERT INTO parenttest (mydata,parent) SELECT 'child1', id FROM parenttest WHERE mydata='rootnode';
INSERT INTO parenttest (mydata,parent) SELECT 'child2', id FROM parenttest WHERE mydata='rootnode';
INSERT INTO parenttest (mydata,parent) SELECT 'child2.1', id FROM parenttest WHERE mydata='child2';
INSERT INTO parenttest (mydata.parent) SELECT 'child2.2', id FROM parenttest WHERE mydata='child2';

I think the right thing to do here is go with client-side, retrieving the id of 'rootnode' after inserting it, and then sending the following two statements with its id explicitly in the INSERT statement.


After some experiments, i've found one solution using an anonymous PL/PgSQL code block:

DO $$DECLARE
  parentid bigint;
BEGIN  
  INSERT INTO parenttest (mydata) VALUES ('rootnode');  
  parentid:=currval('parenttest_id_seq');  
  INSERT INTO parenttest (mydata,parent) VALUES ('child 1',parentid);
  INSERT INTO parenttest (mydata,parent) VALUES ('child 2',parentid);
  parentid:=currval('parenttest_id_seq');
  INSERT INTO parenttest (mydata,parent) VALUES ('child 2.1',parentid),('child 2.2',parentid);
END$$;

It's possible to use it within a single request from the client and i don't have to generate anything at the client-side, so this may be one possible solution for my problem.

Edit: It can be expressed shorter like this:

DO $$DECLARE
  parentid bigint;
BEGIN
  INSERT INTO parenttest (mydata) VALUES ('Flat') RETURNING id INTO parentid;
  INSERT INTO parenttest (mydata, parent) VALUES ('Subflat 1', parentid);
  INSERT INTO parenttest (mydata, parent) VALUES ('Subflat 2', parentid)RETURNING id INTO parentid;
  INSERT INTO parenttest (mydata, parent) VALUES ('Subflat 2.1', parentid), ('Subflat 2.2', parentid);
END$$;

Need Your Help

BizTalk 2013 Start Message Processing Before Source File Finishes?

sql-server biztalk esb biztalk-2013

We've got a large & complicated file that takes a long time to disassemble (say, an hour). It would be great if we could spin off messages as they leave the receive pipeline and start on their