Робота з реляційними базами даних на прикладі PostgreSQL

поле. Ім'я поля в новій таблиці. Імена полів перераховуються в круглих дужках і розділяються комами.

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

обмеження_поля.

3.1.1 Типи даних

varchar (80) задає тип даних, який може зберігати символьні рядки довжиною до 80 символів. int - це звичайний цілочисельний тип. real - це тип даних, що зберігає числа з плаваючою точкою одинарної точності. Тип date говорить сам за себе. (Цілком вірно, поле з типом date так і називається дата. Зручно це чи ні - вирішувати вам.)

PostgreSQL підтримує таки корисні типи SQL як int, smallint, real, double precision, char (N), varchar (N), date, time, timestamp і interval, а також і інші загальні типи і багатий набір геометричних типів. PostgreSQL можна налаштувати так, щоб він працював з довільним числом типів даних, визначених користувачем. Отже, імена типів не є синтаксичними ключовими словами, за виключаємо тих випадків, де потрібні підтримка спеціально згідно стандартуSQL.

3.2 Внесення даних у таблицю

Для додавання записів в таблицю використовується тригер INSERT:

INSERT INTO имя_таблиці [(список_імен_полів)] VALUES (значення1, значення2, …, значення n);

Для завантаження великої кількості даних з простого текстового файлу, ви також можете використовувати команду COPY. Зазвичай це працює швидше, тому що команда COPY оптимізована для операції, яку вона виконує, але в той же час вона менш гнучка ніж команда INSERT. Ось приклад еЈ використання:

COPY weather FROM '/home/user/weather.txt';

де файл, вказаний як джерело даних повинен бути доступний на машині з backend сервером, а не на клієнтській машині, тому що backend сервер читає цей файл безпосередньо. Ви можете прочитати подробиці про команду COPY в PostgreSQL Reference Manual.

3.3 Редагування таблиць

Під час редагування таблиці доводиться виконувати такі роботи:

· зміна даних, тобто редагування вмісту полів;

· маніпуляція записами, їх вилучення і вставка;

· перейменування та знищення таблиці;

· зміна структури таблиці, це вставка та вилучення полів, перейменування полів, додавання та вилучення обмежень тощо.

Змінити дані таблиці дозволяє команда:

UPDATE ім'я_таблиці SET ім'я_поля1=значення1, ім'я_поля2 = значення2, ... WHERE вираз

Приклад. Скласти команду для заміни в таблиці gazpr поля naz_g на Україна тих записів, де kod_g = 2:

UPDATE gazpr SET naz_g = 'Україна' WHERE kod_g = 2;

Якщо редагуються всі записи, то інструкція WHERE може бути опущена. Для нашого прикладу така команда матиме такий вигляд:

UPDATE gazpr SET naz_g = 'Україна';

Знищення всіх або вибраних за умовою записів таблиці забезпечує команда:

DЕLETE FROM ім'я_таблиці WHERE вираз

Приклад. Знищити ті записи таблиці gazpr, яких naz_g = Україна.

DELETE FROM gazpr WHERE gazpr.naz_g = 'Україна';

Вставку записів забезпечує вже розглянена команда INSERT.

Модифікація таблиці командою ALTER TABLE. У більшості сучасних РСУБД передбачена можливість модифікації таблиць командою ALTER TABLE. Її реалізація в PostgreSQL забезпечує такі види модифікації:

· створення полів;

· призначення і скасування значень за умовчанням;

· перейменування таблиці;

· перейменування полів;

· додавання обмежень;

· зміна власника.

Додавання полів. Для створення нового поля в команду ALTER TABLE включається секція ADD COLUMN. Синтаксис команди ALTER TABLE з секцією ADD COLUMN:

ALTER TABLE ім'я_таблиці ADD [COLUMN] ім'я_поля тип_поля

· таблиця - ім'я таблиці, в якій створюється нове поле;

· ім'я_поля - ім'я нового поля;

· тип_поля - тип нового поля.

Ключове слово COLUMN не є обов'язковим і включається в команду лише для наочності. Приклад включення в таблицю books нового поля publication для зберігання дати публікації:

ALTER TABLE books ADD publication date;

Призначення і скасування значень за замовчуванням

ALTER TABLE таблиця ALTER [COLUMN] імя_поля

{SET DEFAULT значення | DROP DEFAULT}

Як і в попередньому розділі, ключове слово COLUMN є необов'язковим і включається в команду лише для наочності. Нижче наведено приклади встановлення та скасування послідовності значень за замовчуванням для поля id таблиці books.

ALTER TABLE books ALTER COLUMN id SET DEFAULT nextval ('books.id');

ALTER TABLE books ALTER id DROP DEFAULT;

Перейменування таблиці забезпечує команда:

ALTER TABLE таблиця RENAME ТО нове_імя

Таблицю можна перейменовувати скільки завгодно разів, це ніяк не відбивається на стані зберігаються в ній даних. Зрозуміло, що в деяких ситуаціях перейменування небажані, зокрема, якщо таблиця використовується зовнішнім додатком. Приклад зміни імені literature на books:

ALTER TABLE literature RENAME TO books;

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

ALTER TABLE таблиця RENAME [COLUMN] імя_поля ТО нове_імя_поля

Як і в інших командах ALTER TABLE, ключове слово COLUMN є необов'язковим. По двох ідентифікаторах, розділеним ключовим словом ТО, PostgreSQL може визначити, що команда перейменування відноситься до одного поля, а не до таблиці. Приклад:

ALTER TABLE daily RENAME COLUMN in_stock TO is_in_stock:

Додавання обмежень. Після створення таблиці зберігаються деякі можливості додавання обмеження. У PostgreSQL команда ALTER TABLE з секцією ADD CONSTRAINT дозволяє визначати для полів існуючих таблиць тільки обмеження зовнішнього ключа. Команда створення нових обмежень має такий синтаксис:

ALTER TABLE таблиця ADD CONSTRAINT ім'я обмеження визначення

Синтаксис визначення залежить від типу обмеження. Нижче показано створення нового обмеження зовнішнього ключа для таблиці editions (пов'язаної з полем id таблиці books) і обмеження перевірки для поля type.

ALTER TABLE editions

ADD CONSTRAINT foreign_book FOREIGN KEY (bookjd) REFERENCES books (id);

ALTER TABLE editions

ADD CONSTRAINT hard_or_paper_back CHECK (type = 'p1 OR type =' h ');

Установка обмеження зовнішнього ключа призводить до того, що будь-яке значення book_id у таблиці editions також має існувати і в таблиці books. Крім того, внаслідок встановленого обмеження перевірки полі type в таблиці editions може містити тільки значення р або b.

Зауважимо, що при створенні унікального індекса командою CREATE INDEX (буде розглянена у відповідному розділі) також неявно встановлюється і обмеження унікальності.

В існуючу таблицю можна додати тільки обмеження таблиць. Це робить така команда:

ALTER TABLE ім'я_таблиці

ADD [ CONSTRAINT ім'я_та_визначення_обмеження ]

Команда ALTER додає обмеження таблиці, але не поля. Нижче встановлюється нове обмеження FOREIGN KEY для поля kod_g таблиці gazpr, яке зв'язується з полем kod_g таблиці oblik. Обмеження FOREIGN KEY гарантує, що внаслідок вставки або оновлення даних у поле kod_g таблиці oblik не з'являться значення, відсутні в полі kod_g таблиці gazpr, тому у прикладі система видала відповідне повідомлення про помилку, тобто про те, що в таблиці gazpr не існує газопроводу з кодом 7.

ALTER TABLE gazpr ADD CONSTRAINT bbb PRIMARY KEY (kod_g);

ALTER TABLE oblik ADD CONSTRAINT aaa

FOREIGN KEY (kod_g) REFERENCES gazpr(kod_g);

INSERT INTO OBLIK VALUES(4,4,'21.12.2000',4);

INSERT INTO OBLIK VALUES(7,4,'21.12.2000',4);

ERROR: insert or update on table "oblik" violates foreign key constraint "aaa"

DETAIL: Key (kod_g)=(7) is not present in table "gazpr".

SQL state: 23503

Зауважимо, що обмеження унікальності також неявно встановлюється при створенні унікального індекса командою CREATE INDEX.

Видалення обмежень. Деякі версії PostgreSQL не підтримують пряме видалення обмежень із таблиці. Добитися потрібного результату можна лише одним способом: створити копію таблиці, що практично повністю повторює оригінал, який не містить тих обмежень, які видаляються. Дані копіюються з початкової таблиці в нову, після чого таблиці перейменовуються командою ALTER TABLE і копія замінює оригінал.

Зміна власника. За замовчуванням творець таблиці автоматично стає її власником. Власник володіє всіма правами, пов'язаними з таблицею, в тому числі правами передання і відкликання прав командами GRANT і REVOKE (будуть розглянені пізніше у відповідному розділі). Зміна власника проводиться командою ALTER TABLE з секцією OWNER. Команда має такий синтаксис:

ALTER TABLE ім'я_таблиці OWNER ТО новий_власник

Змінювати власника таблиці може або поточний власник, або суперкористувач. Приклад зміни власника таблиці employee, де новим власником стає користувач corwin:

ALTER TABLE employees OWNER TO corwin;

Реструктуризація таблиць командами CREATE TABLE і INSERT INTO. Якщо таблиця, створена командою CREATE TABLE AS, не влаштовує (наприклад, якщо в таблиці необхідно встановити обмеження полів), то одну команду CREATE TABLE AS можна замінити двома командами SQL. Спочатку команда CREATE TABLE створює нову таблицю, а потім команда INSERT INTO з запрограмованим SELECT заповнює її даними. Видалення таблиць забезпечує команда:

DROP TABLE таблиця

Використання команди DROP TABLE вимагає обережності, оскільки видалення таблиці спричиняє знищення всіх збережених у ній даних. При знищенні таблиці з неявно створеним індексом знищуються і всі пов'язані з нею індекси.

Копіювання даних із зовнішніх файлів командою COPY. У PostgreSQL підтримується і така корисна можливість, як пряме імпортування даних в таблицю з зовнішніх файлів командою COPY. Файл, з якого беруться дані, зберігається або в стандартному текстовому форматі ASCII з обмеженням полів спеціальним символом-роздільником, або в двійковому форматі таблиць PostgreSQL. У ASCII-файлах як роздільник зазвичай використовується символ табуляції або кома. При імпортуванні данних з ASCII-файлу кожен рядок файлу інтерпретується як окремий запис даних, а кожен компонент рядка - як значення відповідного поля запису.

Команда COPY FROM працює значно швидше за звичайну команду INSERT, оскільки дані передаються прямо в приймальну таблицю за одну транзакцію. З іншого боку, до формату вихідного файлу пред'являються надзвичайно жорсткі вимоги, тому помилка всього в одному рядку спричинить до збою всієї команди COPY. Синтаксис команди COPY FROM:

COPY [BINARY] таблиця [WITH 0IDS]

FROM {'ім'я_файлу' | stdin}

[[USING] DELIMITERS 'роздільник']

[WITH NULL AS `рядок_null']

Параметри команди:

- BINARY. Ознака імпортування вхідних даних із двійкового файлу, раніше створеного командою COPY ТО;

- таблиця. Ім'я таблиці, в яку імпортуються дані;

- WITH 0IDS. З першого рядка файлу завантажуються значення всіх ідентифікаторів OID імпортованої таблиці;

- FROM {'ім'я_файлу' | stdin}. Джерело, з якого PostgreSQL отримує вхідні дані, це може бути файл із заданим ім'ям або стандартний ввід (stdin);

- [USING] DELIMITERS 'роздільник'. Символ, який використовується в якості розділювача при розборі вхідних даних. Не використовується для файлів, виведених у двійковому форматі PostgreSQL;

- WITH NULL AS 'рядок null'. Заданий рядок повинен інтерпретуватися як значення NULL. Не використовується для файлів, виведених у двійковому форматі PostgreSQL.

При підготовці до імпортування файлу слід простежити за тим, щоб він був доступний для читання процесом postmaster (тобто користувачем, який запустив PostgreSQL). Крім того, дозволені тільки абсолютні імена файлів; при спробі передати відносне ім'я відбувається помилка.

При роботі з вхідними файлами в форматі ASCII в секції DELIMITERS передається символ, використаний як роздільник значень полів у рядках файлу. Якщо роздільник не вказаний, PostgreSQL вважає, що значення розділюються символом табуляції. Необов'язкова секція WITH NULL визначає формат, в якому передається значення NULL. Якщо секція відсутня, то PostgreSQL інтерпретує послідовність \N як NULL (наприклад, порожні поля вихідного файлу за замовчуванням інтерпретуються як порожні рядкові константи, а не як NULL).

Якщо дані вводяться вручну або передаються терміналу іншою програмою, в якості джерела в секції FROM можна вказати стандартний файл (stdin). При отриманні даних з стандартного вводу вхідний потік повинен завершуватися послідовністю \. (зворотна коса риска плюс точка), зразу за якою слідує символ нового рядка.

Нижче приведено вміст файлу, виведеного PostgreSQL в форматі ASCII. Поля (3 шт.) розділяються комами, а для відображення значення NULL використовується рядок \nul1. У файлі збережені дані з таблиці subjects.

1,Наука,Productivity Ave

2,Релігія, \null

3,Класика,Academic Rd

4,Комп'ютери,Productivity Ave

5,Cooking,Creativity St

Двійковий формат. Команда COPY також дозволяє виконувати операції введення і виведення з даними в двійковому форматі. Якщо команда COPY FROM містить ключове слово BINARY, то вхідний файл повинен бути створений командою COPY ТО в двійковому форматі PostgreSQL. Двійкові файли завантажуються швидше за ASCII-файли, але на відміну від останніх їх не можна читати і редагувати в простих текстових редакторах.

Системні поля. У PostgreSQL всі таблиці містять системні поля, які залишаються невидимими для користувача і не виводяться при вибірці (якщо службова інформації не запитується спеціально). У системних полях зберігаються метадані, які описують вміст записів, вони такі:

Ідентифікатори записів дозволяють розрізнити два записи з однаковими значеннями полів. Для цього в PostgreSQL передбачений ідентифікатор OID, унікальний в межах таблиці. Приклад:

SELECT *, oid FROM my_list;

Знищення таблиці виконує команда:

DROP TABLE tablename;

3.4 Обмеження

PostgreSQL має декілька варіантів обмеження даних (constraint), які впливають на операції вставки і оновлення. Розглянемо один із них, який полягає в установці обмежень для таблиць і полів. Обмеженням є особливий атрибут таблиці, який встановлює критерії допустимості для вмісту її полів. Дотримання цих правил допомагає запобігти заповненню бази помилковими або невідповідними даними. Обмеження задаються в секції CONSTRAINT при створенні таблиці командою CREATE TABLE. Обмеження полів завжди стосуються лише одного поля, тоді як обмеження таблиць можуть встановлюватися як для одного, так і для декількох полів. У команді CREATE TABLE обмеження полів задаються відразу ж після визначення поля, тоді як обмеження таблиці встановлюється в спеціальному блоці, виділеному комами від усіх визначень полів.

Опис обмеження поля виглядає так:

[ CONSTRAINT обмеження]

{ NOT NULL| UNIQUE | PRIMARY KEY | DEFAULT значення CHECK (умова )|

REFERENCES таблиця [ ( поле) ]

[ MATCH FULL | MATCH PARTIAL ]

[ ON DELETE операція]

[ ON UPDATE операція]

[ DEFERRABLE | NOT DEFERRABLE ]

[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]}

Визначення обмеження слідує в команді CREATE TABLE відразу ж за типом обмежуваного поля і передує комі, яка відокремлює його від наступного поля. Обмеження можуть встановлюватися для будь-якої кількості полів, а ключове слово CONSTRAINT і ідентифікатор обмеження не обов'язкові.

Існує шість типів обмежень полів, які задаються за допомогою спеціальних ключових слів. Деякі з них побічно встановлюються при створені обмежень іншого типу. Типи обмежень полів перераховані нижче, вони такі:

NOT NULL. Поле не може містити псевдозначення NULL. Обмеження NOT NULL еквівалентно обмеженню CHECK (поле NOT NULL);

UNIQUE. Поле не може містити значення, які повторюються. Слід враховувати, що обмеження UNIQUE допускає багатократне входження псевдозначень NULL, оскільки формально NULL не збігається ні з яким іншим значенням;

PRIMARY KEY. Автоматично встановлює обмеження UNIQUE і NOT NULL, а для заданого поля створюється індекс. У таблиці може встановлюватися тільки одне обмеження первинного ключа;

DEFAULT значення. Пропущені значення поля замінюються заданою величиною. Тип значення за замовчуванням повинно відповідати типу поля;

CHECK умова. Команда INSERT або UPDATE завершується успішно лише при виконанні заданої умови (виразу, що повертає логічний результат). При установці обмеження поля в секції CHECK може використовуватися тільки поле, для якого встановлюється обмеження;

REFERENCES. Це обмеження складається з таких секцій:

REFERENCES таблиця [(поле)]. Вхідні значення обмежуваного поля порівнюються із значеннями іншого поля в заданій таблиці. Якщо збіги відсутні, то команда INSERT або UPDATE завершується невдачею. Якщо параметр поле не вказаний, то перевірка виконується за первинним ключем. Обмеження REFERENCES подібне до обмеження таблиці FOREIGN KEY, яке описане в наступному пункті цього підрозділу. Дійсно, між цими обмеженнями є багато спільного. Приклад таблиці, створеної з обмеженням FOREIGN KEY, приведений у лістингу 7.8;

MATCH FULL | MATCH PARTIAL. Секція MATCH указує, чи дозволяється змішувати значення NULL і звичайні значення при вставці в таблицю, у якої зовнішній ключ посилається на декілька полів. Таким чином, на практиці секція MATCH приносить користь лише в обмеженнях таблиць, хоча формально вона може використовуватися і при обмеженні полів. Конструкція MATCH FULL забороняє вставку даних, у яких частина полів зовнішнього ключа містить псевдозначення NULL (крім випадку, коли NULL міститься у всіх полях). У PostgreSQL 7.1.x конструкція MATCH PARTIAL не підтримується. Якщо секція MATCH відсутня, то вважається, що поля з псевдозначепиями NULL задовільняють обмеження. Також буде доречно нагадати, що обмеження полів стосується лише одного поля, тому секція MATCH використовується лише в обмеженнях таблиць;

ON DELETE операція. При виконанні команди DELETE для заданої таблиці з обмежуваним полем виконується одна з таких операцій:

N0 ACTION (якщо видалення спричиняє порушення цілісності посилань, то відбувається помилка; використовується за замовчуванням, якщо операція не вказана),

RESTRICT (аналогічно N0 ACTION),

CASCADE (видалення всіх записів, що містять посилання на запис, який видаляється),

SET NULL (поля, що містять посилання на запис, який видаляється, замінюються псевдозначеннями NULL),

SET DEFAULT (полям, що містять посилання на запис, який видаляється, привласнюється значення за замовчуванням);

ON UPDATE операція. При виконанні команди UPDATE для заданої таблиці виконується одна з вищеописаних операцій. За замовчуванням використовується значення N0 ACTION. Якщо вибрана операція CASCADE, всі записи, які містять посилання на оновлюваний запис, оновлюються новим значенням (замість видалення, як у випадку з ON DELETE CASCADE);

DEFERRABLE | NOT DEFERRABLE. Значення DEFERRABLE дозволяє відкласти виконання обмеження до кінця транзакції (замість негайного виконання після завершення команди). Значення NOT DEFERRABLE означає, що обмеження завжди перевіряється відразу ж після завершення чергової команди. В цьому випадку користувач не може відкласти перевірку обмеження до кінця транзакції. За замовчуванням вибирається саме цей варіант;

INITIALLY DEFERRED | INITIALLY IMMEDIATE. Секція INITIALLY задається лише для обмежень, визначених з ключовим словом DEFERRED. Значення INITIALLY DEFERRED відкладає перевірку обмеження до кінця транзакції, а при установці значення INITIALLY IMMEDIATE перевірка проводиться після кожної команди. За відсутності секції INITIALLY за замовчуванням використовується значення INITIALLY IMMEDIATE.

Обмеження таблиць подібні до обмежень полів, але на відміну від них, можуть стосуватися відразу декількох полів таблиці.

Зауважимо, що деякі версії PostgreSQL забезпечують лише додавання обмежень CHECK і FOREIGN KEY.

Вибір режиму перевірки обмежень забезпечує команда: