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;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
