Настройка разделителей и форматов данных при загрузке

Когда данные приходят из разных систем, их формат может отличаться. Один файл может использовать запятые в качестве разделителей, другой — точки с запятыми, а в третьем табуляция может быть основным способом отделения столбцов. Некорректная настройка разделителей при загрузке данных может привести к ошибкам или неправильной интерпретации данных.

Мало того, в реальной жизни вы можете столкнуться с ситуациями, когда требуется загрузить данные в текстовом формате без стандартных CSV-заголовков, а также обработать пустые значения и предусмотреть случаи, когда пустые строки должны интерпретироваться как NULL. Поэтому настройка разделителей и форматов является неотъемлемой частью работы с массовой загрузкой данных.

Основные разделители: как с ними работать?

В PostgreSQL команда COPY предлагает гибкость в настройке разделителей. Давайте разберем, как это работает.

Установка разделителей

По умолчанию команда COPY ожидает, что столбцы в CSV-файле будут разделены запятыми. Однако это не всегда удобно или возможно: кто-то может использовать точки с запятой (;), вертикальные черты (|) или даже табуляцию (\t).

Вот как можно указать разделитель с помощью параметра DELIMITER:

COPY students FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER;

Если вместо запятых используются точки с запятой:

COPY students FROM '/path/to/students.csv'
DELIMITER ';'
CSV HEADER;

Можно даже загрузить файл с табуляцией:

COPY students FROM '/path/to/students.tsv'
DELIMITER E'\t' 
CSV HEADER;

Здесь E'\t' указывает, что разделителем будет символ табуляции.

Загрузка файлов с нестандартным разделителем

Практический пример: у вас есть файл с данными о курсах, где используются вертикальные черты (|) как разделители. Данные в файле выглядят следующим образом:

course_id|course_name|credits
1|SQL Basics|3
2|Advanced SQL|4
3|PostgreSQL Masterclass|5

Вот как можно загрузить эти данные в таблицу courses:

COPY courses(course_id, course_name, credits)
FROM '/path/to/courses.csv' 
DELIMITER '|' 
CSV HEADER;

Здесь мы явно указываем PostgreSQL, что разделителем является вертикальная черта.

Настройка форматов данных при загрузке

Разделители — это только часть задачи. Формат данных в файле также играет важную роль. Рассмотрим основные способы настройки форматов данных.

Игнорирование пустых строк и задание NULL

Часто в больших массивах данных встречаются пустые значения в полях. В FORMAT CSV PostgreSQL по умолчанию воспринимает неэкранированную пустую строку между разделителями именно как NULL (опция NULL по умолчанию — unquoted empty string). В FORMAT TEXT дефолтное значение NULL — это \N. Если в вашем файле NULL обозначен иначе (например, словом NULL), задайте свой маркер через NULL '...':

Пример. Допустим, в вашем файле есть записи с пустыми значениями:

id,first_name,last_name,email
1,John,Doe,
2,Jane,Smith,jane.smith@example.com
3,,Brown,james.brown@example.com

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.csv'
WITH (FORMAT CSV, HEADER, NULL 'NULL');

А чтобы наоборот заставить парсер сохранить пустые строки как пустые (а не как NULL), можно поставить опцию NULL в какой-нибудь маркер, которого точно нет в данных.

Игнорирование пустых строк

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

Пример. Файл с пустой строкой:

id,first_name,last_name,email
1,John,Doe,john.doe@example.com

2,Jane,Smith,jane.smith@example.com

Если в файле бывают пустые строки, их нужно отфильтровать до запуска COPY (например, утилитой sed '/^$/d' in.csv > out.csv) или загрузить файл в промежуточную таблицу со столбцами TEXT и потом отфильтровать пустые строки в INSERT ... SELECT.

Работа с нестандартным форматом данных

Иногда вам может понадобиться загрузить данные, представленные в текстовом формате, вместо CSV. Например, строки в файле разделены вертикальными чертами |, а данные не имеют строки заголовка.

Пример файла:

1|John|Doe|john.doe@example.com
2|Jane|Smith|jane.smith@example.com

В этом случае можно использовать следующий запрос:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.txt' 
DELIMITER '|' 
NULL AS '' 
CSV;

Если в файле нет заголовков, просто уберите параметр HEADER.

Практический пример настройки форматов данных

Сценарий: у вас есть файл grades.tsv, содержащий оценки студентов. Данные выглядят следующим образом:

student_id  course_id   grade
1   101     85
1   102     90
2   101     78
2   102     88
3   101     95
3   102 

Требуется:

1. Загрузить файл с правильной интерпретацией пустых значений как NULL.
2. Убедиться, что данные загружены корректно.

Решение:

1. Создайте таблицу grades:

CREATE TABLE grades (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    grade INTEGER
);

2. Загрузите данные из файла:

COPY grades(student_id, course_id, grade)
FROM '/path/to/grades.tsv' 
DELIMITER E'\t' 
NULL AS '' 
CSV HEADER;

3. Проверьте загруженные данные:

SELECT * FROM grades;

Ожидаемый результат:

Рекомендации и типичные ошибки

Ошибка: несоответствие разделителя. Если вы не укажете правильный разделитель, PostgreSQL выдаст ошибку или загрузит данные некорректно. Например, если в файле используется точка с запятой, но вы забыли указать DELIMITER ';', команда COPY воспримет всю строку как один столбец.

Ошибка: неправильная интерпретация NULL. Если не указать параметр NULL AS '', пустые значения в файле могут быть интерпретированы как пустые строки, что часто вызывает ошибки в расчетах или фильтрации.

Ошибка: неправильный формат данных. Некорректная установка разделителя или ошибок в файле (например, использование табуляции вместо пробелов) может привести к ошибке вида: ERROR: null value in column violates not-null constraint.

2

Задача

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

Недоступна

Установка нестандартного разделителя при загрузке

Установка нестандартного разделителя при загрузке

Открыть