PostgreSQL Додавання даних командами INSERT та COPY
Додавання даних командами INSERT та COPY
Після створення таблиці із заданою структурою настає наступний етап – заповнення таблиці даними. У PostgreSQL є три загальні способи заповнення таблиць даними:
Вставка нових даних
Нижче наведено синтаксис команди INSERT INTO при вставці нових даних:
INSERT INTO таблиця
[( ім'я_поля [, . ] ) ] VALUES ( значення [. . ] )
Нижче наведено параметри команди.
- таблиці. Ім'я таблиці, до якої вставляються дані командою SQL INSERT.
- (ім'я_поля [. .]). Необов'язковий групований список полів нового запису, яким надаються значення.
- VALUES. Ключове слово SQL, за яким слідує групований список значень.
- (Визначення [, . ]). Обов'язковий список груп значень полів. Для кожного поля вказується одно значення, елементи списку розділяються комами. Елемент списку може бути виразом (наприклад, операцією із двома операндами) або константою.
Тип кожного значення у секції VALUES має відповідати типу поля, якому воно надається. Якщо необов'язковий список полів відсутній, PostgreSQL передбачає, що розділ VALUES містить значення всіх полів у структурі таблиці в порядку їх визначення. Якщо кількість значень менша за кількість полів, PostgreSQL намагається використовувати значення за промовчанням (або NULL за його відсутності) для кожного пропущеного елемента.
У лістингу 4.16 наведено приклад створення нового запису в таблиці books бази даних booktown.
Лістинг 4.16. Вставка нового запису до таблиці books
booktown=# INSERT INTO books (Id, title, author_id, subject_id)
booktown-#VALUES (41472, 'Practical PostgreSQL', 1212, 4);
INSERT 3574037 1
УНаведений приклад необов'язкового списку полів збігається з порядком проходження полів у структурі таблиці (зліва направо). У цьому випадку цей список можна опустити, оскільки команда INSERT припускає, що значення присвоюються в природному порядку дотримання полів таблиці. Поля у списку можна переставити, але в цьому випадку порядок значень у секції VALUES також повинен змінитися, як показано у лістингу 4.17.
Лістинг 4.17. Зміна порядку перерахування полів
booktown=# INSERT INTO books (subjected, author_id, id, title)
booktown-# VALUES (4, 7805, 41473, 'Programming Python');
INSERT 3574041 1
Вставлення даних з інших таблиць командою SELECT
Команда INSERT INTO застосовується і в іншій ситуації - коли дані, що зберігаються в таблиці, вже присутні в іншій таблиці (або кількох таблицях). У цьому випадку команда має наступний синтаксис:
INSERT INTO таблиця
За аналогією із синтаксисом INSERT INTO, представленим у попередньому підрозділі, команда містить необов'язковий список полів, яким надаються нові значення. Проте у цій формі INSERT INTO секція VALUES замінюється повною командою SQL SELECT.
Припустимо, база даних booktown містить таблицю book_queue з інформацією про книги, що очікують надходження у продаж. Після підтвердження дані переносяться з таблиці book_queue до звичайної таблиці books. Приклад розв'язання цього завдання продемонстровано у лістингу 4.18.
Лістинг 4.18. Вставлення даних з іншої таблиці
booktown-# INSERT INTO books (id, title, author_id, subject_id)
booktown-# SELECT nextval('book_ids', title, author_id, subject_id
booktown-# FROM book_queue WHERE approved;
У наведеному прикладі запит SELECT, включений до команди INSERTINTO переносить два записи з таблиці book_queue до таблиці books. У цьому контексті можна використовувати будь-яку синтаксично правильну команду SELECT. У прикладі в вибірку включається результат виклику функції nextval () для послідовності bookj ds, за яким слідують значення полів title, author_id і subject_id з таблиці book_queue.
Цього разу команда створює відразу кілька нових записів, тому в повідомленні про успішне виконання операції замість значення OID, яке виводилося б при вставці одного запису, виводиться 0. Друге число, як і у випадку зі звичайною командою INSERT INTO, дорівнює кількості створених записів ( у разі - 2).
Копіювання даних із зовнішніх файлів командою COPY
У PostgreSQL підтримується і така корисна можливість, як пряме імпортування даних таблицю із зовнішніх файлів командою COPY. Файл, що містить вхідні дані команди COPY, зберігається у стандартному текстовому форматі ASCII з обмеженням полів спеціальним символом-розділювачем, або у двійковому форматі таблиць PostgreSQL. В ASCII-файлах як роздільник зазвичай використовується символ табуляції або кома. При імпортуванні даних з файлу ASCII кожен рядок файлу інтерпретується як окремий запис даних, а кожен компонент рядка - як значення відповідного поля запису.
Команда COPY FROM працює значно швидше за звичайну команду INSERT, оскільки дані передаються прямо в приймальну таблицю за одну транзакцію. З іншого боку, до формату вихідного файлу висуваються надзвичайно жорсткі вимоги, тому помилка всього в одному рядку призводить до збою всієї команди COPY.
Синтаксис команди COPY FROM:
COPY [ BINARY ] таблиця [ WITH 0IDS ] FROM < 'ім'я_файлу' \stdin > [[USING] DELIMITERS'розділювач' ] [ WITH NULL AS 'рядок_nulГ ]
Нижче пояснюється значення параметрів команди.
- BINARY. Ознака імпортування вхідних даних із двійкового файлу, раніше створеного командою COPY TO.
- таблиці. Ім'я таблиці, до якої імпортуються дані.
- WITH OIDS. З першого рядка файлу завантажуються значення всіх ідентифікаторів OID таблиці, що імпортується.
- FROM < 'ім'я_файлу' \stdin >. Джерело, з якого PostgreSQL отримує вхідні дані - файл із заданим ім'ям або стандартне введення (stdin).
- [USING] DELIMITERS 'розділювач'. Символ, що використовується як роздільник при розборі вхідних даних. Не використовується для файлів, виведених у двійковому форматі PostgreSQL.
- WITH NULL AS ' рядок_null. Цей рядок повинен інтерпретуватися як значення NULL. He використовується для файлів, виведених у двійковому форматі PostgreSQL.
Під час підготовки до імпортування файлу простежте, щоб цей файл був доступний для читання процесом postmaster (тобто користувачем, який запустив PostgreSQL). Крім того, дозволено лише абсолютні імена файлів; під час спроби передати відносне ім'я відбувається помилка.
При роботі з вхідними файлами у форматі ASCII у секції DELIMITERS передається символ, який використовується як роздільник значень полів у рядках файлу. Якщо розділювач не вказаний, PostgreSQL вважає, що значення поділяються на символ табуляції. Необов'язкова секція WITH NULL визначає формат, де передаються значення NULL. Якщо секція відсутня, PostgreSQL інтерпретує послідовність \N як NULL (наприклад, порожні поля вихідного файлу за промовчанням інтерпретуються як порожні строкові константи, а не як NULL).
Якщо дані вводяться вручну або передаються терміналуіншою програмою, як джерело в секції FROM можна вказати стандартне введення (stdin). При отриманні даних із стандартного введення вхідний потік повинен завершуватися послідовністю. (зворотна коса риса плюс точка), за якою негайно слідує символ нового рядка.
У лістингу 4.19 наведено вміст файлу, виведеного PostgreSQL у форматі ASCII. Поля розділяються комами, а для представлення NULL використовується рядок \null.B файл збережені дані з таблиці subjects бази даних booktown.
Лістинг 4.19. Приклад копіюваного ASCII-файлу