PostgreSQL – вставлять строки на основе выбора из другой таблицы и обновлять FK в этой таблице с вновь вставленными строками

Я делаю миграцию данных между двумя таблицами (разделение связанной таблицы). Существующая таблица – это reminders , и она имеет start столбец и недавно добавленный столбец dateset_id указывающий на новую таблицу dateset , которая также имеет start столбец. Для каждой строки в reminders я хочу dateset новую строку в dateset с start значением, скопированным и UPDATE соответствующую строку в reminders с недавно вставленным идентификатором dateset .

Вот SQL, который я пробовал:

 WITH inserted_datesets AS ( INSERT INTO dateset (start) SELECT start FROM reminder RETURNING reminder.id AS reminder_id, id AS dateset_id ) UPDATE reminder SET dateset_id = ids.dateset_id FROM inserted_datesets AS ids WHERE reminder.id = ids.reminder_id 

Я получаю сообщение об ошибке missing FROM-clause entry for table "reminder" , потому что я reminder.id столбец reminder.id в предложении RETURNING , но фактически не выбираю его для вставки. Это имеет смысл, но я не могу понять, как изменить запрос, чтобы делать то, что мне нужно. Есть ли совершенно другой подход, который мне не хватает?

Существует несколько способов решения проблемы.

1. временно добавить столбец

Как уже упоминалось, прямой путь заключается в том, чтобы временно добавить столбец reminder_id к dateset . Заполните его оригинальными IDs из таблицы reminder . Используйте его, чтобы присоединиться к reminder с таблицей dateset . Отбросьте временный столбец.

2. Когда старт уникален

Если значения dateset столбца уникальны, это можно сделать без дополнительного столбца, присоединив таблицу reminder таблицей dateset в start столбце.

 INSERT INTO dateset (start) SELECT start FROM reminder; WITH CTE_Joined AS ( SELECT reminder.id AS reminder_id ,reminder.dateset_id AS old_dateset_id ,dateset.id AS new_dateset_id FROM reminder INNER JOIN dateset ON dateset.start = reminder.start ) UPDATE CTE_Joined SET old_dateset_id = new_dateset_id ; 

3. Когда старт не уникален

Это возможно сделать без временной колонки даже в этом случае. Основная идея заключается в следующем. Давайте посмотрим на этот пример:

У нас есть две строки в reminder с одинаковым start значением и идентификаторами 3 и 7:

 reminder id start dateset_id 3 2015-01-01 NULL 7 2015-01-01 NULL 

После того, как мы dateset их в dateset , будут созданы новые идентификаторы, например, 1 и 2:

 dateset id start 1 2015-01-01 2 2015-01-01 

На самом деле не имеет значения, как мы связываем эти две строки. Конечным результатом может быть

 reminder id start dateset_id 3 2015-01-01 1 7 2015-01-01 2 

или

 reminder id start dateset_id 3 2015-01-01 2 7 2015-01-01 1 

Оба эти варианта верны. Это подводит нас к следующему решению.

Просто сначала вставьте все строки.

 INSERT INTO dateset (start) SELECT start FROM reminder; 

Сопоставьте / соедините две таблицы в start столбце, зная, что они не уникальны. «Сделать это» уникальным, добавив ROW_NUMBER и присоединившись к двум столбцам. Можно сделать запрос короче, но я прописал каждый шаг явно:

 WITH CTE_reminder_rn AS ( SELECT id ,start ,dateset_id ,ROW_NUMBER() OVER (PARTITION BY start ORDER BY id) AS rn FROM reminder ) ,CTE_dateset_rn AS ( SELECT id ,start ,ROW_NUMBER() OVER (PARTITION BY start ORDER BY id) AS rn FROM dateset ) ,CTE_Joined AS ( SELECT CTE_reminder_rn.id AS reminder_id ,CTE_reminder_rn.dateset_id AS old_dateset_id ,CTE_dateset_rn.id AS new_dateset_id FROM CTE_reminder_rn INNER JOIN CTE_dateset_rn ON CTE_dateset_rn.start = CTE_reminder_rn.start AND CTE_dateset_rn.rn = CTE_reminder_rn.rn ) UPDATE CTE_Joined SET old_dateset_id = new_dateset_id ; 

Я надеюсь, что из кода ясно, что он делает, особенно когда вы сравниваете его с более простой версией без ROW_NUMBER . Очевидно, что комплексное решение будет работать, даже если start уникален, но он не так эффективен, как простое решение.

Это решение предполагает, что перед этим процессом dateset не заполнен.

Вот еще один способ сделать это, отличный от трех способов, предложенных Владимиром до сих пор.

Временная функция позволит вам прочитать идентификатор созданных новых строк, а также другие значения в запросе:

 --minimal demonstration schema CREATE TABLE dateset ( id SERIAL PRIMARY KEY, start TIMESTAMP -- other things here... ); CREATE TABLE reminder ( id SERIAL PRIMARY KEY, start TIMESTAMP, dateset_id INTEGER REFERENCES dateset(id) -- other things here... ); --pre-migration data INSERT INTO reminder (start) VALUES ('2014-02-14'), ('2014-09-06'), ('1984-01-01'), ('2014-02-14'); --all at once BEGIN; CREATE FUNCTION insertreturning(ts TIMESTAMP) RETURNS INTEGER AS $$ INSERT INTO dateset (start) VALUES (ts) RETURNING dateset.id; $$ LANGUAGE SQL; UPDATE reminder SET dateset_id = insertreturning(reminder.start); DROP FUNCTION insertreturning(TIMESTAMP); ALTER TABLE reminder DROP COLUMN start; END; 

Этот подход к проблеме предложил себя после того, как я понял, что написание INSERT ... RETURNING в качестве подзапроса разрешит проблему; хотя INSERT не разрешены в качестве подзапросов, безусловно, вызовы функций.

Интригующе это говорит о том, что подзапросы DML, возвращающие значения, могут быть в целом полезными. Если бы они были возможны, мы просто напишем:

 UPDATE reminder SET dateset_id = ( INSERT INTO dateset (start) VALUES (reminder.start) RETURNING dateset.id)); 

Вы можете возвращать столбцы только с помощью RETURNING из INSERT-части, а не из выбранной таблицы. Итак, если вы хотите добавить столбец reminder_id в свою таблицу дат,

  ALTER TABLE dateset ADD COLUMN reminder_id integer; 

следующее заявление будет работать:

 WITH inserted_datesets AS ( INSERT INTO dateset (start, reminder_id) SELECT start, id FROM reminder RETURNING reminder_id, id AS dateset_id ) UPDATE reminder SET dateset_id = ids.dateset_id FROM inserted_datesets AS ids WHERE id = reminder_id 

Только если значения столбца, начинающегося в напоминаниях , уникальны , также будут работать следующие 2 оператора:

 INSERT INTO dateset(start) SELECT start FROM reminder; UPDATE reminder SET dateset_id = (SELECT id FROM dateset WHERE start=reminder.start); 

Проблема в том, что вы можете возвращать только столбцы, которые присутствуют в таблице, в которую вы вставляете. Вы можете решить это, предоставив таблице данных дополнительный столбец, в который вы вставляете reminder.id, чтобы вы могли его вернуть.

Затем после миграции вы можете удалить этот столбец.