Обновить таблицу с возвращенным идентификатором из вставки в другую таблицу

Я пытаюсь понять, как вставить данные из таблицы 1 в таблицу 2, а затем использовать вновь созданный идентификатор из таблицы 2 для обновления соответствующей строки в таблице 1.

Я использую Postgres 12.4 для чего он стоит

Пример: у меня есть две таблицы, например users и metadata

В таблицах пользователей есть следующие столбцы

| id | info | metadata_id |

В таблице метаданных есть следующие столбцы

| id | data |

Я хочу перенести все мои значения info из таблицы users в столбец data таблицы metadata и обновить свой users.metadata_id (в настоящее время пустой) соответствующими значениями metadata.id, по сути, путем обратного заполнения внешних ключей.

Есть ли способ сделать это изящно? У меня есть рабочий запрос, который блокирует обе таблицы и создает временную последовательность для вставки в metadata.id и users.metadata_id, но это кажется хрупким, и мне нужно будет запускать последовательность после самого высокого существующего идентификатора в таблице метаданных, который не идеальный.

Я также пытался использовать CTE, изменяющий данные, с предложением RETURNING для обновления таблицы users, но не смог заставить это работать.


postgresql sql-insert common-table-expression sql-update postgresql-12
person Rocket Appliances    schedule 10.09.2020    source источник
comment
Цепной CTE - лучший вариант. Пожалуйста, разместите свой код.   -  person wildplasser    schedule 11.09.2020

Ответы (1)


arrow_upward
1
arrow_downward

Вы не можете использовать здесь returning, так как вам нужно отслеживать ассоциации пользователей и метаданных при вставке.

Я думаю, что проще сначала предварительно сгенерировать серийный номер метаданных каждого пользователя в CTE, используя nextval(). Затем вы можете использовать эту информацию для вставки в метаданные и обновления таблицы пользователей:

with 
    candidates as (
        select u.*, nextval(pg_get_serial_sequence('metadata', 'id')) new_metadata_id
        from users u
    ),
    inserted as (
        insert into metadata (id, data) overriding system value
        select new_metadata_id, info from candidates
    )
update users u
set metadata_id = c.new_metadata_id
from candidates c
where c.id = u.id

Нам нужно предложение overriding system value в операторе insert, поэтому Postgres позволяет нам писать в столбец serial.

Демонстрация DB Fiddle

person GMB    schedule 10.09.2020