sql - Insert inserted id to another table

Question

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 insert to a do also insert into b (a_id) values (new.id);

I'm trying to create a record in b referencing to a on insertion to a table. But what I get is that new.id is null, as it's automatically generated from a sequence. I also tried a trigger AFTER insert FOR EACH ROW, but result was the same. Any way to work this out?

Answer 1

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

WITH ins_a AS (
   INSERT INTO a(val)
   VALUES ('foo')
   RETURNING a_id
   )
INSERT INTO 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