CODEWITHSUNDEEP
sqlpostgresqlunionsql-insertcommon-table-expression
Jade Dezo
Jade Dezo

Reputation: 429

SQL - Extend outcome of INSERT query with additional query in Postgres

I have a simple INSERT query based on a CTE:

WITH my_values ( ...)
INSERT INTO my_table
SELECT * FROM my_values WHERE ...
RETURNING *;

I would like to extend the output with another query. Something like this:

WITH my_values ( ...)
SELECT * FROM (
    INSERT INTO my_table
    SELECT * FROM my_values WHERE ...
    RETURNING *
) AS t1
UNION ALL
SELECT ...;

Is this possible in Postgres? I am trying several queries, but keep getting syntax errors.

update

Even the following query is not working. I get syntax error at/near 'INTO':

WITH my_values ( ...)
SELECT * FROM (
    INSERT INTO my_table
    SELECT * FROM my_values WHERE ...
    RETURNING *
) AS t1;

I'll try to have the INSERT into a CTE as well, but I would like to know what is wrong with above query.

Upvotes: 2

Views: 163

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I guess you have to use CTEs. Try this:

WITH my_values ( ...),
     i as (
      INSERT INTO my_table
          SELECT * FROM my_values WHERE ...
          RETURNING *
    )
SELECT *
FROM i;

Upvotes: 1

Related Questions