Insert Inserted Id To Another Table

Here's the scenario: create table a ( id serial primary key, val text ); create table b ( id serial primary key, a_id integer references a(id) ); create rule a_inserted as on

Solution 1:

To keep it simple, you could also just use a data-modifying CTE (and no trigger or rule):

WITH ins_a AS (
   INSERTINTO a(val)
   VALUES ('foo')
   RETURNING a_id
   )
INSERTINTO b(a_id)
SELECT a_id
FROM   ins_a
RETURNING b.*;  -- last line optional if you need the values in return

Related answer with more details:

• PostgreSQL multi INSERT...RETURNING with multiple columns

Or you can work with currval() and lastval():

• How to get the serial id just after inserting a row?
• Reference value of serial column in another column during same INSERT

Solution 2:

Avoid rules, as they'll come back to bite you.

Use an after trigger on table a that runs for each row. It should look something like this (untested):

createfunction a_ins() returnstriggeras $$
begininsertinto b (a_id) values (new.id);
  returnnull;
end;
$$ language plpgsql;

createtrigger a_ins after inserton a
foreachrowexecuteprocedure a_ins();

Solution 3:

Don't use triggers or other database Kung fu. This situation happens every moment somewhere in the world - there is a simple solution:

After the insertion, use the LASTVAL() function, which returns the value of the last sequence that was auto-incremented.

Your code would look like:

insertinto a (val) values ('foo');
insertinto b (a_id, val) values (lastval(), 'bar');

Easy to read, maintain and understand.