Re: Combining INSERT with DELETE RETURNING

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Combining INSERT with DELETE RETURNING
Дата
Msg-id CAKFQuwbJThWgzEO6hUNFwgbctSn8kvS+y-J74yjP2gntVbk8+Q@mail.gmail.com
обсуждение исходный текст
Ответ на Combining INSERT with DELETE RETURNING  (Alexander Farber <alexander.farber@gmail.com>)
Список pgsql-general
Дерево обсуждения
On Fri, Mar 24, 2017 at 8:06 AM, Alexander Farber <alexander.farber@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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Alexander Farber
Дата:
Сообщение: Combining INSERT with DELETE RETURNING
Следующее
От: Thomas Kellerer
Дата:
Сообщение: Re: Combining INSERT with DELETE RETURNING