INSERT INTO ... FROM SELECT ... ВОЗВРАТ

Question

Я использую PostgreSQL 9.3.

Я хочу дублировать некоторые записи БД. Поскольку я использую pk id с автоматическим приращением для таблицы, я хочу вернуть сопоставления идентификаторов из сгенерированных идентификаторов дублированных записей в исходные. Например, скажем, у меня есть стол posts с двумя записями:

 [{'id': 1, 'title': 'first'}
, {'id': 2. 'title': 'second'}]

С SQL:

INSERT INTO posts (title) SELECT title FROM posts RETURNING id, ??

Я ожидаю увидеть сопоставления как:

 [{'id': 3, 'from_id': 1}
, {'id': 4, 'from_id': 2}]

Любая идея о том, как заполнить вопросительные знаки выше, чтобы это работало? Большое спасибо!

Answer 1

Это было бы проще для UPDATEгде дополнительные строки, включенные в обновление, видны RETURNING пункт:

• Возврат значений столбцов pre-UPDATE с использованием только SQL - версия PostgreSQL

То же самое в настоящее время не возможно для INSERT, По документации:

Выражение может использовать любые имена столбцов таблицы с именем table_name

имя_таблицы является целью INSERT команда.

Вы можете использовать (изменяющие данные) CTE, чтобы заставить это работать.
Если предположить, title чтобы быть уникальным для запроса, иначе вам нужно сделать больше:

WITH sel AS (
   SELECT id, title
   FROM   posts
   WHERE  id IN (1,2)   -- select rows to copy
   )
,    ins AS (
   INSERT INTO posts (title)
   SELECT title FROM sel
   RETURNING id, title
 )
SELECT ins.id, sel.id AS from_id
FROM   ins
JOIN   sel USING (title);

Если title не уникален для запроса (но по крайней мере id является уникальным для таблицы):

WITH sel AS (
   SELECT id, title, row_number() OVER (ORDER BY id) AS rn
   FROM   posts
   WHERE  id IN (1,2)   -- select rows to copy
   ORDER  BY id
   )
,    ins AS (
   INSERT INTO posts (title)
   SELECT title FROM sel ORDER  BY id  -- ORDER redundant to be sure
   RETURNING id
 )
SELECT i.id, s.id AS from_id
FROM  (SELECT id, row_number() OVER (ORDER BY id) AS rn FROM ins) i
JOIN   sel s USING (rn);

Этот второй запрос основан на недокументированной детализации реализации, что строки вставляются в указанном порядке. Он работает во всех текущих версиях Postgres и, вероятно, не сломается.

SQL Fiddle.

Answer 2

Если id столбец posts создано nextval('posts_id_seq'::regclass)Вы можете вручную вызвать эту функцию для каждой новой строки

with
sel as (
  SELECT id, title, nextval('posts_id_seq'::regclass) new_id
  FROM   posts
  WHERE  id IN (1,2)
),
ins as (
  INSERT INTO posts (id, title)
  SELECT new_id, title
  FROM sel
)
SELECT id, new_id
FROM sel

работает с любыми данными, включая неуникальные title

Answer 3

ИМХО, самое простое решение - просто добавить в таблицу столбец, в который можно поместить идентификатор клонированной строки.