Импорт данных из CSV-файлов с использованием команды `COPY`

Привет, друзья! Представьте, что у вас есть таблица Excel с 10 000 строк, и начальник говорит: "Загрузи-ка это в базу!" Хм...если вы попытаетесь вставлять данные вручную, то… скорее всего, вам понадобятся не только SQL-навыки, но и отпуск. 😅

К счастью, у PostgreSQL есть команда, которая работает как телепорт из CSV в базу — это COPY. Она загружает данные мгновенно, массово и без лишнего шума. Сегодня мы разберёмся, как работает COPY, как подготовить данные, и что делать, если в файле вдруг оказывается "плавающая запятая" или нестандартный символ. Поехали!

Основы команды COPY

COPY — это команда PostgreSQL, которая позволяет перемещать данные между таблицей и файловой системой. Она полезна для массовой загрузки данных в базу или для их экспорта.

Пример синтаксиса COPY для загрузки данных в таблицу:

COPY table_name FROM 'path/to/your/file.csv' DELIMITER ',' CSV HEADER;

Основные параметры:

• FROM 'path/to/your/file.csv': определяет путь к вашему файлу CSV.
• DELIMITER ',': указывает символ-разделитель (в данном случае это запятая).
• CSV HEADER: указывает, что в файле CSV есть заголовок (первая строка с именами столбцов).

Команда COPY работает непосредственно на сервере, что делает её чрезвычайно быстрой при работе с большими объёмами данных.

Различие между COPY и \COPY

Иногда вы можете столкнуться с небольшой путаницей между COPY и \COPY.

• COPY: выполняется на стороне сервера. Используется для загрузки файлов, которые находятся на сервере.
• \COPY: клиентская команда, доступная в psql. Загрузка данных осуществляется с вашего локального компьютера.

Если вы работаете на локальной машине, скорее всего, будете использовать \COPY. Мы поговорим о ней чуть позже.

Пример использования COPY

Давайте сразу перейдём к примеру, чтобы на практике лучше понять принцип работы.

Шаг 1: Подготовка таблицы

Представьте, что мы создаём таблицу для хранения информации о студентах:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE,
    grade FLOAT
);

Шаг 2: Подготовка CSV-файла

Формат вашего CSV-файла должен совпадать с таблицей. Пример файла students.csv:

first_name,last_name,date_of_birth,grade
John,Doe,2001-05-15,85.5
Jane,Smith,2000-12-22,90.0
Alice,Johnson,2002-03-10,78.0
Bob,Brown,2001-08-30,NULL

Обратите внимание: порядок столбцов в CSV должен соответствовать столбцам, указанным в таблице, а типы данных должны совпадать (например, дата должна быть в формате YYYY-MM-DD).

Шаг 3: Загрузка данных

Для загрузки файла students.csv в таблицу students используйте следующую команду:

COPY students (first_name, last_name, date_of_birth, grade)
FROM '/path/to/your/students.csv'
DELIMITER ',' 
CSV HEADER;

Что здесь происходит?

• Мы указываем имя таблицы students и столбцы, в которые загружаются данные.
• Путь к файлу указывает на его местоположение.
• Разделитель , указывает, что данные разделяются запятыми.
• Параметр CSV HEADER сообщает PostgreSQL, что первая строка файла содержит названия столбцов.

Результат:

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

SELECT * FROM students;

Ограничения и особенности

Чтобы избежать ошибок, убедитесь, что ваш CSV-файл соответствует следующим требованиям:

1. Отсутствие скрытых символов или пробелов в строке заголовка.
2. Использование правильной кодировки: в PostgreSQL обычно рекомендуется UTF-8.
3. Совпадение разделителя и указанного параметра DELIMITER.

Типичные ошибки при загрузке COPY:

Несоответствие структуры данных. Например, если вы пытаетесь загрузить текстовое значение в числовое поле:

ERROR: invalid input syntax for type numeric: "abc"

Чтобы избежать таких проблем, убедитесь, что данные соответствуют ожидаемому типу.

Ошибка пути к файлу.

Если файл находится вне доступной директории PostgreSQL на сервере, вы получите ошибку:

ERROR: could not open file "/path/to/your/file.csv" for reading: Permission denied

Решением может быть правильная настройка прав доступа к файлу.

Как проверить, что данные загружены корректно?

После загрузки данных полезно убедиться, что всё прошло успешно. Например:

Проверка общего количества строк:

SELECT COUNT(*) FROM students;

Поиск пустых значений (например, если поле grade содержит NULL):

SELECT * FROM students WHERE grade IS NULL;

Полезные советы для работы с COPY

Логируйте ошибки. Если вы хотите записывать ошибки в отдельный файл, используйте параметр LOG ERRORS. (В PostgreSQL версии 12+).

Отключайте индексы и триггеры.

При загрузке больших объёмов данных можно временно отключить индексы:

ALTER TABLE students DISABLE TRIGGER ALL;

Используйте транзакции.

Это позволяет загрузить данные "атомарно" — либо они загрузятся полностью, либо откатятся в случае ошибки:

BEGIN;
COPY students FROM '/path/to/your/file.csv' CSV HEADER;
COMMIT;

Практическое применение

Умение пользоваться командой COPY пригодится не только администраторам баз данных — разработчикам она тоже может здорово облегчить жизнь. Представьте, что вы получаете большой объём данных из стороннего API: сначала сохраняете их в CSV-файл, а потом быстро загружаете в PostgreSQL — и можно начинать анализ. Или, скажем, коллеги просят перенести их старую базу в новую систему на PostgreSQL. В таких ситуациях COPY становится настоящей палочкой-выручалочкой.

CSV-файлы встречаются в реальных проектах постоянно. Например:

• Вы загружаете статистику продаж в аналитическую систему.
• Импортируете список пользователей из внешней CRM.
• Переносите данные из Excel в PostgreSQL без лишних танцев с бубном.

На этом мы завершаем знакомство с командой COPY. В следующей лекции поговорим о том, как подготовить таблицы для загрузки данных и продумать их структуру так, чтобы всё работало быстро и надёжно. А пока не бойтесь экспериментировать — COPY открывает действительно удобные возможности для работы с большими объёмами информации.

2

Задача

SQL SELF, 23 уровень, 1 лекция

Недоступна

Импорт данных в таблицу с использованием `COPY`

Импорт данных в таблицу с использованием `COPY`

Открыть