записываемое общее табличное выражение и несколько операторов insert

#sql #postgresql #common-table-expression

Вопрос:

Как мне написать следующее в действительном запросе Postgres SQL:

 with foo as (select * from ...)
insert into bar select * from foo
insert into baz select * from foo

Ответ №1:

Вы можете использовать CTE, если хотите, чтобы все это было в одном операторе:

 with foo as (
      select * from ...
     ),
     b as (
      insert into bar
          select * from foo
          returning *
     )
insert into baz
    select * from foo;

Примечания:

Вы должны включить списки столбцов с insert .
Вы должны явно указать имена столбцов для select * . Это важно, потому что столбцы могут не совпадать в двух таблицах.
Я всегда использую returning with update / insert / delete в CTE. Это обычный вариант использования — например, вы можете получить серийные идентификаторы обратно из вставки.

1. это выдает мне следующую ошибку: ОШИБКА: синтаксическая ошибка в или около «(» СТРОКА 73: b как ( ^ Состояние SQL: 42601 Символ: 1778 @Gordon Linoff

2. @Sam . . . В этом ответе нет строки 73. Если у вас есть вопрос, задайте новый вопрос.

3. да, извините, я пытался реализовать эту логику в своем коде, оказывается, я использовал псевдоним для ‘ foo’, который выдал мне ошибку ‘b as (‘ но когда я удалил псевдоним, это сработало! @Gordon Linoff

Ответ №2:

Область действия with предложения — это всего лишь один запрос. Единственное решение, которое я могу придумать, — это создать представление и удалить его, когда вы закончите вставку. Это не будет полностью эфемерным, как CTE, но здесь нет дублирования данных — просто (относительно) дешевая операция DDL:

 -- Create the view
CREATE VIEW foo AS SELECT * FROM ...;

-- Perform the inserts
INSERT INTO bar SELECT * FROM foo;
INSERT INTO baz SELECT * FROM foo;

-- Drop the view when you're done
DROP VIEW foo;