postgresql: nested insert

Question

I have two tables. Lets say tblA and tblB.
I need to insert a row in tblA and use the returned id as a value to be inserted as one of the columns in tblB.

I tried finding out this in documentation but could not get it. Well, is it possible to write a statement (intended to be used in prepared) like

INSERT INTO tblB VALUES 
(DEFAULT, (INSERT INTO tblA (DEFAULT, 'x') RETURNING id), 'y')

like we do for SELECT?

Or should I do this by creating a Stored Procedure?. I'm not sure if I can create a prepared statement out of a Stored Procedure.

Please advise.

Regards,
Mayank

Denis de Bernardy · Accepted Answer · 2011-05-11 16:37:37Z

7

You'll need to wait for PostgreSQL 9.1 for this:

with
ids as (
insert ...
returning id
)
insert ...
from ids;

In the meanwhile, you need to use plpgsql, a temporary table, or some extra logic in your app...

answered May 11, 2011 at 16:37

Denis de Bernardy

79.2k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 3 · Accepted Answer · 2011-05-11 17:34:03Z

This is possible with 9.0 and the new DO for anonymous blocks:

do $$
declare 
  new_id integer;
begin
  insert into foo1 (id) values (default) returning id into new_id;
  insert into foo2 (id) values (new_id);
end$$;

This can be executed as a single statement. I haven't tried creating a PreparedStatement out of that though.

Edit

Another approach would be to simply do it in two steps, first run the insert into tableA using the returning clause, get the generated value through JDBC, then fire the second insert, something like this:

PreparedStatement stmt_1 = con.prepareStatement("INSERT INTO tblA VALUES (DEFAULT, ?) returning id");
stmt_1.setString(1, "x");
stmt_1.execute(); // important! Do not use executeUpdate()!
ResultSet rs = stmt_1.getResult();
long newId = -1;
if (rs.next()) {
   newId = rs.getLong(1);
}
PreparedStatement stmt_2 = con.prepareStatement("INSERT INTO tblB VALUES (default,?,?)");
stmt_2.setLong(1, newId);
stmt_2.setString(2, "y");
stmt_2.executeUpdate();

Wayne Conrad · Accepted Answer · 2011-05-11 19:51:59Z

2

You can do this in two inserts, using currval() to retrieve the foreign key (provided that key is serial):

create temporary table tb1a (id serial primary key, t text);
create temporary table tb1b (id serial primary key,
                             tb1a_id int references tb1a(id),
                             t text);
begin;
insert into tb1a values (DEFAULT, 'x');
insert into tb1b values (DEFAULT, currval('tb1a_id_seq'), 'y');
commit;

The result:

select * from tb1a;
 id | t
----+---
  3 | x
(1 row)

select * from tb1b;
 id | tb1a_id | t
----+---------+---
  2 |       3 | y
(1 row)

Using currval in this way is safe whether in or outside of a transaction. From the Postgresql 8.4 documentation:

currval

Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.

edited May 11, 2011 at 19:51

answered May 11, 2011 at 17:51

Wayne Conrad

109k26 gold badges158 silver badges195 bronze badges

4 Comments

Miki Over a year ago

Maybe I am paranoid, but relying on currval is not the best idea. First, it assumes that the id is based on sequence. Second, unless explicitly using transactions the value of the key may change between the inserts, which screws up the integrity. Third, it is not reusable. But, that is only my opinion and you do not need to agree with it :)

Wayne Conrad Over a year ago

@Sorrow, I've edited the answer to address your concern about the value of the key changing. To the other charges, I plead nolo contendere.

Miki Over a year ago

@Waynce, your edit clarifies the issue. Therefore, my comment should now state "relying on currval is a good option provided that the id is tied to a sequence". The third concern, though, still holds - but it is a design issue not included in the question and may start an endless debate, so better stop it here.

Wayne Conrad Over a year ago

@Sorrow, I agree, it's completely and utterly non-portable. In any case, thanks for helping to make the answer better.

Miki · Accepted Answer · 2011-05-11 18:52:23Z

1

You may want to use AFTER INSERT trigger for that. Something along the lines of:

create function dostuff() returns trigger as $$
begin
 insert into table_b(field_1, field_2) values ('foo', NEW.id);
 return new; --values returned by after triggers are ignored, anyway
end;
$$ language 'plpgsql';

create trigger trdostuff after insert on table_name for each row execute procedure dostuff();

after insert is needed because you need to have the id to reference it. Hope this helps.

Edit

A trigger will be called in the same "block" as the command that triggered it, even if not using transactions - in other words, it becomes somewhat part of that command.. Therefore, there is no risk of something changing the referenced id between inserts.

edited May 11, 2011 at 18:52

answered May 11, 2011 at 17:47

Miki

7,2382 gold badges34 silver badges42 bronze badges

Collectives™ on Stack Overflow

postgresql: nested insert

4 Answers 4

Comments

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related