CODEWITHSUNDEEP
node.jspostgresql
Chris Cirefice
Chris Cirefice

Reputation: 5815

Postgres insert into table multiple return values from with rows as

I'd like to insert multiple (2) rows into a table in Postgres, and use the ids returned by WITH ROWS AS in an insert to another table (columns id_1 and id_2 in second_table), and return that id as the final result of the query.

first_table is the main data collection, and second_table essentially just links two first_table entries together.

Here's the gist of what I'm trying to do:

WITH row AS (
        INSERT INTO first_table (some_data, more_data)
        VALUES (DEFAULT, DEFAULT), (DEFAULT, DEFAULT)
        RETURNING first_table_id
        )
INSERT INTO second_table (id_1, id_2, other_data)
VALUES (???, ???, DEFAULT)
RETURNING second_table_id

I am using Node.js and the node-pg module; I know that this can be done in Node.js by having the first query return the two rows returned by WITH ROWS AS, then running another prepared statement using the two ids for the next insert.

I'm not sure if I can do this through a trigger, since ON INSERT only has one row. Maybe it would have two since these rows are inserted into first_table in the same transaction, but I don't have extensive experience with Postgres/SQL in general so I'm not sure how I could do this via trigger.

The problem I'm having is that the WITH ROWS AS statement returns 2 rows (intended, obviously), but I can't figure out how to access each row independently in the next insert, and I have a feeling that it isn't possible.

Can this be accomplished in a single query, or do I have to use a trigger or Node.js for this?

Upvotes: 2

Views: 3309

Answers (1)

pozs
pozs

Reputation: 36274

The second insert needs to be an INSERT INTO ... SELECT ... FROM first_cte. If you can make an assumtion, that the second generated id is larger than the first (or at least, distinct & comparable -- this is the case, if you use PostgreSQL's serial, or directly a sequence), you can use aggregation:

WITH rows AS (
  INSERT INTO first_table (some_data, more_data)
  VALUES (DEFAULT, DEFAULT), (DEFAULT, DEFAULT)
  RETURNING first_table_id
)
INSERT INTO second_table (id_1, id_2)
SELECT min(first_table_id), max(first_table_id)
FROM rows
RETURNING second_table_id

Note that, other_data is omitted: you cannot use the DEFAULT in INSERT INTO ... SELECT ....

For a more general solution, you can use arrays:

WITH rows AS (
  INSERT INTO first_table (some_data, more_data)
  VALUES (DEFAULT, DEFAULT), (DEFAULT, DEFAULT)
  RETURNING first_table_id
)
INSERT INTO second_table (id_1, id_2, other_data)
SELECT first_table_ids[1], first_table_ids[2], 5 -- value '5' for "other_data"
FROM (
  SELECT array_agg(first_table_id) AS first_table_ids
  FROM rows
) AS rows_as_array
RETURNING second_table_id

Upvotes: 3

Related Questions