Можно ли полностью заменить содержимое одной таблицы на содержимое другой без предварительного удаления содержимого таблицы-приемника

Question

Задача полностью скопировать одну таблицу в другую, при этом в таблице-приемнике есть данные, которые должны быть полностью заменены на новые. Т. е., старые данные должны быть стерты и заменены на новые. Аналогии с операционной системой подсказывали мне, что должен быть запрос, который просто перезапишет одну таблицу поверх другой (т.е., если мы один файл копируем в другой, то нам не надо заботится о том, что находится в файле-приемнике, он просто будет перезаписан). Однако, я не нашел подобной механики. Надо либо дропнуть приемник и создать его заново с копированием источника:

DROP TABLE dest;
CREATE TABLE dest AS TABLE source;

либо удалить содержимое приемника и скопировать содержимое источника:

TRUNCATE TABLE dest;
INSERT INTO dest (fields) SELECT fields FROM source;

Два вопроса:

Существует ли способ обновить данные без предварительного стирания предыдущих.
Если такого способа нет, то какой из предложенных мной способов лучше и чем? Возможно существует еще какой-то?

Answer 1

В PostgreSQL нет прямого способа "обновить" содержимое одной таблицы содержимым другой без предварительного удаления данных в таблице-приемнике.
Из предложенных вами способов лучше TRUNCATE TABLE ...; с последующим INSERT INTO ... (...)SELECT ... FROM ...;

2.1 Сохранение структуры: TRUNCATE TABLE удаляет все данные из таблицы, но сохраняет ее структуру (ограничения, индексы, триггеры). Это важно, если таблица участвует в каких-либо зависимостях или имеет сложную структуру. DROP TABLE удалит всю таблицу, включая ее структуру.

2.2 Скорость: В большинстве случаев TRUNCATE TABLE работает быстрее, чем DROP TABLE с последующим CREATE TABLE. Это связано с тем, что TRUNCATE TABLE просто удаляет записи, не записывая их в журнал транзакций. Особенно это заметно для больших таблиц.
Альтернативный (не оптимальный) способ, почти тоже самое что и пункт 2

DELETE FROM dest;
INSERT INTO dest (id, fields) SELECT id, fields FROM source;

P.S. в некоторых случаях DROP TABLE с последующим CREATE TABLE может быть более эффективным, например, если вам необходимо скопировать данные из таблицы с одной схемой в другую схему. В таких случаях создание новой таблицы может быть быстрее, чем INSERT в существующую таблицу.

P.P.S. На всякий случай прикладываю документацию по командам postgrespro https://postgrespro.ru/docs/postgresql/16/sql-droptable https://postgrespro.ru/docs/postgresql/16/sql-createtable https://postgrespro.ru/docs/postgresql/16/sql-truncate https://postgrespro.ru/docs/postgresql/16/sql-delete

Answer 2

Дисклеймер: я уже выбрал лучший ответ, однако, я нашел альтернативное решение, которое, по моему мнению, может пригодиться не только мне. Мне оно помогло, поэтому оставлю его здесь

Альтернативой таблице-приемнику может стать так называемое материализованное представление - Materialized view - слепок данных из таблицы-источника, который может быть как полной таблицей, так и выборкой. В этом случае, мы сначала создаем представление:

CREATE MATERIALIZED VIEW dest
AS TABLE source;

или для выборки

CREATE MATERIALIZED VIEW dest
AS SELECT … FROM source …;

и после этого, в момент когда нам необходимо перезаписать старое содержимое новым применяем:

REFRESH MATERIALIZED VIEW dest;

Также оставлю здесь ссылку на мой вопрос о разнице между временной таблицей и materialized view - в ответе подробно описана разница.

Answer 3

Оператор MERGE вставит новые, заменит конфликты, но оставит без изменения то что нет в таблице обновления. Удобен например для обновления частями.

БЛОГ НА HUSL

Можно ли полностью заменить содержимое одной таблицы на содержимое другой без предварительного удаления содержимого таблицы-приемника

Ответы (3 шт):