PostgreSQL multi INSERT...RETURNING with multiple columns

Question

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?

For WITH INSERT UPDATE - with t as (insert ... returning ...) update ... set ...; syntax, refer to stackoverflow.com/a/45465626/8690463 — Y Y
– Y Y, Commented Sep 19, 2021 at 7:22

Erwin Brandstetter · Accepted Answer · 2020-05-23 22:34:28Z

16

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 typically best to add a column definition list for INSERTs (except for special cases). Else, if the table structure changes, your code might break in surprising ways.
I omitted columns where you would just enter DEFAULT. Defaults are inserted automatically. Shorter, same result.
The final, optional RETURNING returns the resulting user_id - obviously from a sequence or some other default. It's actually the user_id from table3, but that's the same unless you have some triggers or other magic interfering.

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

Are SELECT type queries the only type that can be nested?

edited May 23, 2020 at 22:34

answered Apr 1, 2014 at 1:37

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

user3483211 Over a year ago

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...

Mahydul Islam Shajal · Accepted Answer · 2021-06-15 05:36:20Z

Insert master-detail using function

CREATE OR REPLACE FUNCTION apps.usp_bazar_list_insert_1(
    ip_userid character varying,
    ip_mobileno character varying,
    ip_bazarlisttext text,
    data_details text)
    RETURNS TABLE(id integer) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
    
AS $BODY$
DECLARE master_id integer;
BEGIN
insert into apps.bazar_list(action_by,mobile_no,bazarlisttext,action_date) 
select ip_userId,ip_mobileNo,ip_bazarListText,now() returning list_id into master_id;

insert into apps.bazar_list_images(list_id,action_date,image_name) 
select master_id,now(),image_name from json_populate_recordset(NULL::apps.bazar_list_images, 
                                   data_details::json);
RETURN QUERY
        select 1;
    
END;
$BODY$;

Collectives™ on Stack Overflow

PostgreSQL multi INSERT...RETURNING with multiple columns

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related