Перенос данных между таблицами в Postgres: команда COPY

Быстрый ответ

Чтобы эффективно перемещать данные между таблицами в PostgreSQL, вы можете использовать мета-команду \COPY:

1. Экспортируйте данные из исходной таблицы в файл CSV:

   SQL

   Скопировать код

   \COPY source_table TO 'file.csv' CSV HEADER;

   Запустить код

2. Импортируйте данные из файла CSV в целевую таблицу:

   SQL

   Скопировать код

   \COPY target_table FROM 'file.csv' CSV HEADER;

   Запустить код

Такой подход позволяет сохранить формат CSV и заголовки столбцов. Работать с командой \COPY можно напрямую из клиентского интерфейса, например, из psql, и не требуется доступ к файлам на сервере.

Детальное руководство по переносу данных

Способ переноса данных зависит от конкретных условий задачи. Рассмотрим разные варианты:

Клонирование таблицы (структура и данные)

CREATE TABLE new_table AS SELECT * FROM existing_table;

Эта команда создаёт new_table, которая содержит все данные и структуру existing_table.

Подробнее об этом расскажет наш спикер на видео

Копирование определенных столбцов

INSERT INTO new_table(colA, colB) SELECT col1, col2 FROM existing_table;

Здесь мы выбираем определенные столбцы (col1, col2) из existing_table и вставляем их в new_table (colA, colB), учитывая их соответствие.

Сначала создание структуры, затем копирование данных

CREATE TABLE new_table (LIKE existing_table INCLUDING ALL);  
INSERT INTO new_table SELECT * FROM existing_table;

Сначала создаётся структура new_table, аналогичная existing_table, после чего данные из последней копируются в новую.

Копирование части данных, соответствующих определенному условию

INSERT INTO new_table SELECT * FROM existing_table WHERE condition;

При использовании данной инструкции в new_table попадут только те строки из existing_table, которые соответствуют заданному условию.

Исключение дубликатов при копировании

INSERT INTO new_table
SELECT DISTINCT ON (unique_column) * FROM existing_table;

Таким образом вы перенесёте в new_table только уникальные данные столбца unique_column из existing_table.

Транзакционная защита

BEGIN;  

CREATE TABLE new_table (LIKE existing_table INCLUDING ALL);
INSERT INTO new_table SELECT * FROM existing_table;

-- Проверка данных перед финальным шагом
SELECT COUNT(*) FROM new_table;  

COMMIT; -- или выполняйте ROLLBACK, если произошла ошибка

Указанные операции выполняются как одна транзакция: или все операции прошли успешно, или все откатываются обратно.

Создание резервной копии таблицы до внесения изменений

pg_dump -t existing_table -f table_backup.sql database_name  
psql database_name -f table_backup.sql

Создаём резервную копию выбранной таблицы перед внесением важных изменений.

Продвинутые методики копирования данных

\COPY – это хороший способ для переноса данных через файлы, однако в PostgreSQL есть и другие методики, позволяющие более детально управлять процессом.

Копирование данных с применением условных фильтров

SELECT * INTO new_table FROM existing_table WHERE condition;

Копирование сложных наборов данных

CREATE TABLE new_table AS
SELECT a.*, b.specific_column
FROM existing_table a
JOIN another_table b ON a.match_column = b.match_column;

Визуализация

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

[Исходная таблица] 🛤️ ===(🚂 Команда COPY)🔄💾===> [Целевая таблица]

Также, как и при транспортировке грузов, команда COPY эффективно перемещает ценные данные с одной "платформы" на другую.

COPY (SELECT * FROM origin_table) TO '/path/to/file.csv' WITH CSV; 
COPY destination_table FROM '/path/to/file.csv' CSV;

Защита и оптимизация процесса переноса данных

Подготовка перед импортом

Перед началом импорта убедитесь, что вам необходимо:

• Отключить индексацию.
• Отключить триггеры.
• Установить дополнительные ограничения.

Анализ после импорта

После импорта данных:

• Убедитесь, что данные правильного качества и количество соответствует ожидаемому.
• Если индексация была отключена, включите её обратно.
• Включите ранее деактивированные триггеры.

Полезные материалы

1. PostgreSQL: Документация: 16: COPY
2. postgresql – Optimizing a Postgres query with a large IN – Database Administrators Stack Exchange
3. Диалог импорта/экспорта данных — документация pgAdmin 4 8.2