Re: Combining INSERT with DELETE RETURNING

Alexander Farber schrieb am 24.03.2017 um 16:06:

> 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."
>
> So I am trying to rewrite the INSERT .. ON CONFLICT DO NOTHING followed by a DELETE:
> into a single statement:
>
> INSERT INTO words_reviews (
> uid,
> author,
> nice,
> review,
> updated
> ) VALUES (
> DELETE FROM words_reviews
> WHERE author <> out_uid
> AND author = ANY(_uids)
> RETURNING
> uid,
> out_uid, -- change to out_uid
> nice,
> review,
> updated
> )
> ON CONFLICT DO NOTHING;

You need a CTE:

with deleted as (
DELETE FROM words_reviews
WHERE author <> out_uid
AND author = ANY(_uids)
RETURNING
uid,
out_uid, -- change to out_uid
nice,
review,
updated
)
INSERT INTO words_reviews (uid, author, nice, review, updated)
select *
from deleted
ON CONFLICT DO NOTHING;