0

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 sequence. I also tried it with trigger, that is launched AFTER insert FOR EACH ROW on a table, but result was the same. Any way to work this out?

1

You can utilize the returning keyword which will return the value of the created serial column. If you wrap you Insert in a With expression, you can then access the returned id and insert into the second table.

WITH getval(id) as
    (INSERT INTO table_a (some_col) VALUES (some_val) RETURNING id) 
INSERT into table_b (id) SELECT id from getval;

where id is the value of the serial resulting from the insert into table a. More in the docs, though it is a somewhat hidden feature.

Note, you can also use returning * to return the entire updated row, not just the serial column, should you need it.

0

in MySQL you can create a trigger. I tried on my db and it works like a champ.

CREATE TRIGGER update_id_on_b AFTER INSERT ON a 
FOR EACH ROW 
INSERT INTO b SET a_id = NEW.id; 

INSERT INTO a VALUES(148,'test 1'); 

SELECT * FROM b;
  • I don't think this addresses the OP's issue because it's likely caused by serial. – mustaccio Dec 21 '14 at 2:05
  • How does this solve anything? – Ben Dec 21 '14 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.