Re: Combining INSERT with DELETE RETURNING

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Fri, Mar 24, 2017 at 8:06 AM, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:

Good afternoon,

the doc https://www.postgresql.org/docs/9.6/static/sql-delete.html states:

"The syntax of the RETURNING list is identical to that of the output list of SELECT."

​[...]​

words=> \i words_merge_users.sql
psql:words_merge_users.sql:218: ERROR:  syntax error at or near "FROM"
LINE 131:                 DELETE FROM words_reviews

​That doesn't work for SELECT either...

INSERT INTO tbl_trg (id)

VALUES (

SELECT 1

);

ERROR: syntax error at or near "SELECT"

This should help with the SELECT variation:

https://www.postgresql.org/docs/9.5/static/sql-insert.html

That said the page does say:

"query

A query (SELECT statement) that supplies the rows to be inserted. Refer to the SELECT statement for a description of the syntax.

"

So directly replace the actual SELECT query with a DELETE-RETURNING doesn't seem to work.  You will need to perform the DELETE separately and then funnel those records through a SELECT statement.  A subquery may work though a CTE is likely considered best practice.

David J.