ПРИХОДИТЕ УЧИТЬСЯ НОВОЙ ПРОФЕССИИ ЛЕТОМ СО СКИДКОЙ ДО 70%Забронировать скидку

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

Пройдите тест, узнайте какой профессии подходите и получите бесплатную карьерную консультацию
В конце подарим скидку до 55% на обучение
Я предпочитаю
0%
Работать самостоятельно и не зависеть от других
Работать в команде и рассчитывать на помощь коллег
Организовывать и контролировать процесс работы

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

Чтобы эффективно перемещать данные между таблицами в 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, и не требуется доступ к файлам на сервере.

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

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

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

SQL
Скопировать код
CREATE TABLE new_table AS SELECT * FROM existing_table;

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

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

SQL
Скопировать код
INSERT INTO new_table(colA, colB) SELECT col1, col2 FROM existing_table;

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

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

SQL
Скопировать код
CREATE TABLE new_table (LIKE existing_table INCLUDING ALL);  
INSERT INTO new_table SELECT * FROM existing_table;

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

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

SQL
Скопировать код
INSERT INTO new_table SELECT * FROM existing_table WHERE condition;

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

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

SQL
Скопировать код
INSERT INTO new_table
SELECT DISTINCT ON (unique_column) * FROM existing_table;

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

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

SQL
Скопировать код
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, если произошла ошибка

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

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

sh
Скопировать код
pg_dump -t existing_table -f table_backup.sql database_name  
psql database_name -f table_backup.sql

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

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

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

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

SQL
Скопировать код
SELECT * INTO new_table FROM existing_table WHERE condition;

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

SQL
Скопировать код
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 как организованный конвейер грузов:

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

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

SQL
Скопировать код
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