PostgreSql INSERT FROM SELECT RETURNING ID

Question

In PostgreSql 9.2.4 I have two tables: user (id, login, password, name) and dealer (id, user_id).

And I want to insert into both tables returning id of created dealer.

Currently I'm doing it with two queries:

WITH rows AS (
    INSERT INTO "user"
        (login, password, name)
    VALUES
        ('dealer1', 'jygbjybk', 'Dealer 1')
    RETURNING id
)
INSERT INTO dealer (user_id)
    SELECT id
    FROM rows;
SELECT currval('dealer_id_seq');

But can I implement this with a single INSERT query using RETURNING statement?

mu is too short · Accepted Answer · 2013-10-03 19:40:37Z

154

You just need to add a RETURNING id to your INSERT ... SELECT:

WITH rows AS (...)
INSERT INTO dealer (user_id)
    SELECT id
    FROM rows
    RETURNING id;

Demo: http://sqlfiddle.com/#!12/75008/1

answered Oct 3, 2013 at 19:40

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

Comments

10 Rep · Accepted Answer · 2021-06-09 16:45:32Z

25

For my purposes, I needed it in a variable so I did this:

INSERT INTO dealer (user_id)
    SELECT id
    FROM rows
    RETURNING id INTO l_dealerid;

edited Jun 9, 2021 at 16:45

10 Rep

2,2687 gold badges21 silver badges34 bronze badges

answered Jun 9, 2021 at 16:44

Blair Kjenner

3493 silver badges5 bronze badges

Collectives™ on Stack Overflow

PostgreSql INSERT FROM SELECT RETURNING ID

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related