CODEWITHSUNDEEP
postgresql
oorst
oorst

Reputation: 979

Return fields from query used for INSERT values

When using a SELECT query to produce values for an INSERT like this:

INSERT INTO some_table (
  foo,
  bar
)
SELECT
  at.foo,
  at.bar
FROM another_table at;

How to return the fields from the SELECT query in the RETURNING clause along with the newly INSERTED rows? Something like:

INSERT INTO some_table AS t (
  foo,
  bar
)
SELECT
  at.foo,
  at.bar
FROM another_table at
RETURNING t.id, at.id;

I'm sure I've done this before or I've overlooked something really obvious.

Is it possible to do this? If so, how?

Upvotes: 1

Views: 157

Answers (2)

oorst
oorst

Reputation: 979

It is not possible to return the fields from the query that provides the values for an INSERT. You can only return fields from the newly inserted rows.

An UPDATE can return fields from the updated rows as well as from the from list of the query.

Upvotes: 0

S-Man
S-Man

Reputation: 23676

demo: db<>fiddle

WITH selecting AS (
    SELECT id, x FROM a 
), inserting AS (
    INSERT INTO b AS b (y) 
    SELECT x FROM selecting
    RETURNING id, y
)
SELECT
    i.id, 
    s.id
FROM 
    inserting i
JOIN
    selecting s
ON i.y = s.x

You can try a CTE approach. First selecting all relevant data, store the result internally. After that do the INSERT statement with these data, store the RETURNING values internally. After all you can you all saved data, combine them with a join and print out what you want.

Upvotes: 2

Related Questions