CODEWITHSUNDEEP
sqlpostgresqlinsertcommon-table-expression
Guest Posting
Guest Posting

Reputation: 395

Storing Multiple Insert IDs

After scouring the site (and others...), I cannot find an example of an insert command allowing me to store the "RETURNING" values to a table, CTE, etc. This is what I'd like to do:

WITH insert_rows AS (
  INSERT INTO employers (column1, column2, insert_date)
  SELECT distinct tc.column1, 'any text', now()
  FROM _tmp_employer_updates tc
  LEFT JOIN employers e ON e.column1 = tc.column1
  WHERE e.column1 IS NULL   -- Only insert non-existing employer names
  RETURNING employer.row_uuid, employer.column1, employer.column2;
  )
SELECT * FROM insert_rows; -- table of returning values

Is there anyway to get an insert command to store it's "returning" values to a table using a CTE? When I try the example above I get:

ERROR: syntax error at or near "INSERT" LINE 1: ... _tmp_inserted_employers AS WITH insert_rows AS ( INSERT INT...

Thanks in advance...

Upvotes: 0

Views: 50

Answers (1)

roman
roman

Reputation: 117510

Remove ; after returning ..., remove alias employer before columns in returning (or change it to employers). Otherwise your query looks good.

Here's an example on sql fiddle.

Upvotes: 1

Related Questions