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;