How to pass a return value from one PostgreSQL query to another?

Question

I have two queries. The first creates a new user. The table has three fields, id, name and email. The id field is an autoincrement value.

The second query creates additional data related to this user, using the ID value.

How can I pass the RETURNING value of the first to the second query?

Example:

INSERT INTO users (
  name, 
  email
) VALUES (
  'John Doe',
  '[email protected]'
) RETURNING id;

INSERT INTO users_stuff (
  user_id,
  money,
  hasCar
) VALUES (
  id,
  1000,
  false
);

The above example doesn't work because the second query never receives the first one's RETURNING value, which would be the new record's autoincrement ID.

Is there a way though?

Gordon Linoff · Accepted Answer · 2021-02-17 20:57:24Z

2

Use a CTE:

WITH u AS (
      INSERT INTO users (name, email)
          VALUES ('John Doe', '[email protected]')
          RETURNING id;
     )
INSERT INTO users_stuff (user_id, money, hasCar) 
    SELECT u.id, 1000, false
    FROM u;

answered Feb 17, 2021 at 20:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

How to pass a return value from one PostgreSQL query to another?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related