Re: [GENERAL] Combining INSERT with DELETE RETURNING

2017-03-24 Thread Thomas Kellerer 
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

Re: [GENERAL] Combining INSERT with DELETE RETURNING

2017-03-24 Thread David G. Johnston 
On Fri, Mar 24, 2017 at 8:06 AM, Alexander Farber <
alexander.far...@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.