Використання \COPY для завантаження даних з локального комп'ютера

Команда \COPY призначена для завантаження даних у PostgreSQL безпосередньо з твого локального комп'ютера. На відміну від COPY, яка працює з файлами на сервері PostgreSQL, команда \COPY працює з файлами, доступними на стороні клієнта, наприклад, на твоєму ноутбуці чи робочому комп'ютері.

Команда \COPY використовується тоді, коли ти працюєш з файлами на своєму локальному комп'ютері, наприклад, ноутбуці. На відміну від COPY, яка читає файли із сервера, \COPY дозволяє завантажувати дані, які знаходяться прямо під рукою.

Це зручно у випадках, коли ти розробляєш або тестуєш проект у себе на машині, або коли не маєш доступу до файлової системи самого сервера PostgreSQL. Такий підхід особливо популярний серед розробників та аналітиків, яким треба швидко підвантажити CSV-файл для перевірки чогось без зайвих заморочок.

Відмінність між COPY та \COPY

На перший погляд команди COPY і \COPY схожі, але принцип їхньої роботи різний:

• COPY виконується на стороні сервера і вимагає, щоб файл знаходився у директорії, доступній для PostgreSQL. Потрібно, щоб PostgreSQL мав права на читання вказаного файлу.

• \COPY виконується на стороні клієнта, тобто файл має бути доступний клієнтському застосунку, наприклад psql. Використовуючи \COPY, ти можеш завантажувати дані з файлів, які знаходяться на твоєму комп'ютері, навіть якщо не маєш доступу до сервера.

Основи використання \COPY

Синтаксис команди

\COPY таблиця FROM 'file_path' [WITH] (опції)

Де:

• таблиця: ім'я таблиці, у яку завантажуються дані.
• file_path: шлях до файлу на твоєму локальному комп'ютері.
• опції: не обов'язкові параметри, які дозволяють кастомізувати поведінку функції.

Приклад:

\COPY students FROM 'C:/data/students.csv' DELIMITER ',' CSV HEADER;

• students: ім'я таблиці, у яку завантажуються дані.
• 'C:/data/students.csv': шлях до файлу на твоєму локальному комп'ютері.
• DELIMITER ',': вказує символ-роздільник даних у файлі (кома в цьому випадку).
• CSV HEADER: вказує, що перший рядок файлу містить заголовки стовпців.

Приклад:

Припустимо, у нас є файл students.csv з даними про студентів, який знаходиться у твоїй локальній папці, наприклад, C:/data/students.csv. Ось його вміст:

id,name,age,major
1,John Doe,20,Computer Science
2,Jane Smith,22,Mathematics
3,Emily White,21,Physics

Ми хочемо завантажити ці дані у таблицю students. Попередньо я створив таблицю в PostgreSQL з такою структурою:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER NOT NULL,
    major TEXT
);

Тепер використовуємо команду \COPY для завантаження даних:

\COPY students FROM 'C:/data/students.csv' DELIMITER ',' CSV HEADER;

Після виконання цієї команди таблиця students міститиме три записи з файлу students.csv. Можеш перевірити це так:

SELECT * FROM students;

Очікуваний результат:

Параметри команди \COPY

При використанні \COPY, особливо у форматі CSV, важливо розуміти ключові параметри, які дозволяють контролювати формат даних і спростити обробку. Нижче — найпопулярніші з них.

Параметр FORMAT

Визначає формат даних. Найчастіше використовуються значення: text, csv.

Приклад:

\COPY users FROM 'users.csv' WITH (FORMAT csv)

Навіщо потрібен: без цього параметра PostgreSQL вважатиме, що ти використовуєш текстовий формат з табуляцією. csv — більш універсальний і читабельний.

Параметр HEADER

Вказує, що перший рядок CSV-файлу містить заголовки стовпців, а не самі дані.

Приклад:

\COPY users FROM 'users.csv' WITH (FORMAT csv, HEADER)

Навіщо потрібен: дозволяє виключити заголовок з імпорту — особливо корисно при експорті з Excel чи інших систем.

Параметр DELIMITER

Задає символ-роздільник полів. За замовчуванням — кома для CSV, табуляція для TEXT.

Приклад:

\COPY products FROM 'products.csv' WITH (FORMAT csv, DELIMITER ';')

Навіщо потрібен: дозволяє адаптувати імпорт/експорт під нестандартні CSV-формати, наприклад з крапкою з комою (часто використовується в Європі).

Параметр ENCODING

Вказує кодування файлу.

Приклад:

\COPY clients FROM 'clients.csv' WITH (FORMAT csv, HEADER, ENCODING 'WIN1251')

Навіщо потрібен: дозволяє коректно завантажувати файли у кодуванні Windows чи інших систем без ручної перекодування.

Параметр NULL

Визначає, яке рядкове значення у файлі буде вважатися NULL.

Приклад:

\COPY orders FROM 'orders.csv' WITH (FORMAT csv, NULL 'NULL')

Навіщо потрібен: при читанні файлів, де відсутні значення представлені рядком 'NULL', це допомагає правильно інтерпретувати порожні поля.

Обмеження та особливості використання \COPY

1. Вимоги до клієнта PostgreSQL

psql — це клієнт PostgreSQL, який підтримує команду \COPY. Переконайся, що ти використовуєш саме його для роботи з базою даних. Майте на увазі, що інші клієнти, наприклад, pgAdmin, можуть не підтримувати \COPY.

2. Кодування файлу

Зверни увагу на кодування файлу, який ти завантажуєш. PostgreSQL очікує, що файл буде у UTF-8. Якщо файл має інше кодування (наприклад, Windows-1251), можуть виникнути помилки. Ти можеш перетворити кодування файлу утилітами, такими як iconv або текстовими редакторами (наприклад, VS Code).

3. Локалізація шляху до файлу

На Windows шлях до файлу може містити зворотні слеші (\). У цьому випадку заміни їх на прямі (/). Наприклад, замість C:\data\students.csv використовуй C:/data/students.csv.

Типові помилки та їх вирішення

1. Помилка доступу до файлу.

Якщо бачиш щось типу:

could not open file "C:/data/students.csv" for reading: No such file or directory

Перевір:

• Файл існує за вказаним шляхом.
• Ти вказав правильний абсолютний шлях.
• У тебе є права доступу до файлу.

2. Помилка невідповідності структури даних. Якщо структура файлу, що завантажується, відрізняється від структури таблиці, отримаєш помилку. Наприклад, якщо файл містить додаткові стовпці або невірні типи даних, з'явиться повідомлення про помилку. Перевір заголовки стовпців і їх типи перед завантаженням.

3. Проблеми з кодуванням. Якщо файл не у UTF-8, побачиш "незрозумілі" символи замість тексту. Рішення — конвертувати файл у UTF-8 перед завантаженням.

Переваги використання \COPY

1. Простота використання. Ти можеш завантажити дані з файлу всього однією командою. Не треба переносити файли на сервер.

2. Універсальність. psql доступний на всіх основних операційних системах, що дозволяє використовувати \COPY практично всюди.

3. Відсутність проблем з доступом. Оскільки команда працює на стороні клієнта, не треба перейматися правами доступу до серверних файлів.

Обмеження та рекомендації

Попри зручність, команда \COPY має деякі обмеження:

• Вона не підходить для дуже великих файлів, бо вимагає передачі даних через мережу між клієнтом і сервером. У таких випадках ефективніше використовувати COPY на сервері.
• Переконайся, що твій файл повністю підготовлений до завантаження: виправлені помилки, видалені зайві рядки, а структура файлу збігається з таблицею.

Для великих проектів рекомендується спочатку протестувати завантаження на невеликих наборах даних і тільки потім обробляти повний файл.