current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I`m building a database with Postgres 9.3 as a backend, having 3 tables:

table1 (user_id, username, name, surname, emp_date)
table2 (pass_id, user_id, password)
table3 (user_dt_id, user_id, adress, city, phone)

As can be seen table2 and table3 are child tables of table1.
I can extract the user_id of a newly inserted row in table1 (parent):

INSERT INTO "table1" (default,'johnee','john','smith',default) RETURNING userid;

I need to insert the newly extracted id (from table1) into user_id columns of table2 and table3 along with other data unique for those tables. Basically 3 X INSERT ...
How do I do that?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Use data-modifying CTEs to chain your three INSERTs. Something like this:

WITH ins1 AS (
   INSERT INTO table1 (username, name,  surname)
   VALUES ('johnee','john','smith')
   RETURNING user_id
   )
, ins2 AS (
   INSERT INTO table2 (user_id, password)
   SELECT ins1.user_id, 'secret'
   FROM   ins1                            -- nothing to return here
   )
INSERT INTO table3 (user_id, adress, city, phone)
SELECT ins1.user_id, ...
FROM   ins1
RETURNING user_id;

  • It's recommended to always add a column definition list for INSERTs (except for ad-hoc code). Else, if the table structure changes, your code might break in surprising ways.

  • I omitted the columns from the column definition list where you would just enter DEFAULT. Defaults are applied automatically. Shorter, same result.

  • The final RETURNING is optional if you want the resulting user_id returned (obviously from a sequence or some other default. It actually returns the user_id from table3, but that's the same unless you have some triggers or other magic interfering.

More details about data-modifying (a.k.a. "writable") CTEs:

share|improve this answer
1  
Thank you ,that`s exactly what i was looking for..also easy explained,i assume these can me nested for any operation (DELETE,UPDATE,...) in a same manner,also last returning not needed,...anyway,spot on and understandable... –  user3483211 Apr 3 '14 at 23:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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