Re: Combining INSERT with DELETE RETURNING

On Fri, Mar 24, 2017 at 8:06 AM, Alexander Farber <
alexander(dot)farber(at)gmail(dot)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.