Postgres Pro
Downloads
Home   >   mailing lists

Re: Combining INSERT with DELETE RETURNING - Mailing list pgsql-general

From David G. Johnston
Subject Re: Combining INSERT with DELETE RETURNING
Date
Msg-id CAKFQuwbJThWgzEO6hUNFwgbctSn8kvS+y-J74yjP2gntVbk8+Q@mail.gmail.com
Whole thread Raw
In response to Combining INSERT with DELETE RETURNING  (Alexander Farber <alexander.farber@gmail.com>)
List pgsql-general
Tree view
On Fri, Mar 24, 2017 at 8:06 AM, Alexander Farber <alexander.farber@gmail.com> 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.

pgsql-general by date:

Previous
From: Alexander Farber
Date:
Subject: Combining INSERT with DELETE RETURNING
Next
From: Thomas Kellerer
Date:
Subject: Re: Combining INSERT with DELETE RETURNING