2.3 PostgreSQL

У 1996 році було вирішено, що ім'я "Postgres95" не відповідає сьогоденню. Було вибрано нове ім'я PostgreSQL щоб підкреслити відмінність від оригінального POSTGRES і вихід багатьох версій з підтримкою SQL.

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

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

· Були реалізовані такі можливості як підзапити, значення по замовчуванню і тригери.

· Були додані можливості для сумісності зі стандартом SQL92, включаючи первинні ключі, ідентифікатори запитів, створення типів, а також двійкове та шістнадцяткове введення цілих чисел.

· Були покращені вбудовані типи даних, включаючи нові широкодіапазонні типи дати / часу і додаткові геометричні типи даних.

· Швидкість роботи backend коду була збільшена приблизно на 20-40%, а час запуску backend'а було скорочено на 80% в порівнянні з версією 6.0.

3. 
   ОСНОВНІ КОНЦЕПЦІЇ РОБОТИ З POSTGRESQL
   

3.1 Створення таблиць

PostgreSQL - це система управління реляційними базами даних (СКБД). Це означає, що це система для управління даними, які зберігаються у вигляді відношень (кортежів). Поняття зберігання даних в таблицях є сьогодні таким банальним, що воно може здатися самоочевидним, однак є кілька інших способів організації баз даних. Файли і каталоги в Unixподібних операційних системах є прикладом ієрархічної бази даних. Одне з найбільш сучасних напрямків розробки СУБД - це об'єктно-орієнтовані бази даних.

Кожна таблиця є пойменованої колекцією рядків (rows) . Кожен запис у таблиці має деякий набір пойменованих колонок (columns) І кожне поле є певним типом даних. Поля в записі розташовані в фіксований порядку, а SQL ніколи не гарантує упорядкованого проходження записів в таблиці.

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

CREATE [ TEMPORARY | TEMP ] TABLE ім’я_таблиці (

{ ім’я_поля тип [ обмеження_поля [...] ] | обмеження таблиці } )

[ INHERITS ( базова_таблиця [ , ... ] ) ] ; де

• 
  TEMPORARY | TEMP. Ознака тимчасової таблиці. Таблиця, створена з ключовим словом TEMPORARY або TEMP автоматично знищується наприкінці поточного сеансу Всі конструкції рівня таблиці (наприклад, індекси і обмеження) знищуються наприкінці сеансу разом з таблицею. Якщо ім’я тимчасової таблиці співпадає з ім’ям існуючої таблиці, то всі посилання на таблицю з цим ім’ям впродовж сеансу відноситимуться до тимчасової таблиці. Іноді це викликає проблеми, оскільки тимчасова таблиця побічно заміщає існуючу таблицю в контексті поточного сеансу до моменту її знищення.
  
• 
  таблиця. Ім’я створюваної таблиці.
  
• 
  поле. Ім’я поля в новій таблиці. Імена полів перераховуються в круглих дужках і розділяються комами.
  
• 
  тип. Відразу ж після імені користувача задається його тип – стандартний тип або масив одного із стандартних типів.
  
• 
  обмеження_поля.
  

базова_таблиця. Ім’я таблиці, від якої нова таблиця успадковує поля. Якщо імена успадкованих полів збігаються з іменами полів, раніше включених в структуру таблиці, PostgreSQL видає повідомлення про помилку і перериває виконання команди.

Типи даних

Числові типи
smallint	коротке 2-х байтовое ціле
integer	звичайне 4-х байтовое ціле
bigint	велике 8-байтовое ціле
decimal	коротке із фіксованою точкою
numeric	коротке із фіксованою точкою
real	коротке із рухомою точкою
double precision	коротке із рухомою точкою подвійної точності
serial	ціле із автозбільшенням
bigserial	велике ціле із автозбільшенням
Грошові типи
money			гроші
Символьні типи
character varying(n), varchar(n)		рядок змінної довжини
character(n), char(n)		рядок фіксованої довжини
text		рядок змінної необмеженої довжини
Бінарні типи
bytea	бінарний рядок змінної довжини
Дата і час
timestamp [ (p) ] [ без часового пояса ]				дата і час
timestamp [ (p) ] с часовим поясом				дата і час із часовим поясом
interval [ (p) ]				Інтервал часу
date				тільки дата
time [ (p) ] [ без часового пояса ]				тільки час
time [ (p) ] с часовым поясом				тільки час із часовим поясом
Логічні типи
boolean	TRUE або FALSE
Геометричні типи
point	Точка на площині (x,y)
line	Невидима лінія (не повністю реалізовано)
lseg	Видимий відрізок ((x1,y1),(x2,y2))
box	Чотирикутник ((x1,y1),(x2,y2))
path	Замкнений прямокутник ((x1,y1),...)
path	Ламана лінія [(x1,y1),...]

polygon	Полігон ((x1,y1),...)
circle	Круг (x,y),r (центр і радіус)
Типи для адрес комп’ютерних мереж
cidr	IPv4 або IPv6 мережа
inet	IPv4 або IPv6 хост і мережа
macaddr	MAC адреса
Бітові рядки
bit [ (n) ]		Бітовий рядок фіксованої довжини
bit varying [ (n) ]		Бітовий рядок змінної довжини
Типи для пошуку тексту
tsquery	Запит на пошук тексту
tsvector	Список для пошуку тексту
UUID тип
uuid	Універсальний унікальний ідентифікатор
XML типи
xml			данні XML

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

Приклад створення таблиці міста, яка містить назви міст і відповідні їм географічні координати:

CREATE TABLE cities ( name varchar(80), location point );

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

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

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

INSERT INTO weather VALUES

( 'Lviv', 46, 50, 0.25, '2014-11-27');

Важливо те, що всі типи даних, що використовуються в команді мають відповідні формати. Константи, які не є простими числовими значеннями зазвичай повинні бути укладені в одинарні лапки ('), як показано у прикладі. Тип date фактично може бути записаний порізному, але ми будемо дотримуватися зрозумілого формату, який зображений у прикладі.

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 заповнює її даними. Приклад реструктуризації таблиці командами CREATE TABLE і INSERT INTO:

CREATE TABLE newbooks ( id integer UNIQUE, title text NOT NULL, authorid integer, subjected integer CONSTRAINT books_id_pkey PRIMARY KEY (id));

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index, books_id_pkey1 for table 'new_books "

INSERT INTO new books

SELECT id, title. author_id, subjected FROM books;

ALTER TABLE books RENAME TO old_books;

ALTER TABLE new_books RENAME TO books;

Видалення таблиць забезпечує команда: DROP TABLE таблиця

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

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

Поле	Опис
oid	4-байтовий унікальний ідентифікатор запису
tableoid	Ідентифікатор таблиці. Ім’я таблиці зв’язується з ідентифікатором у системній таблиці pg_class
xmin	Ідентифікатор транзакції вставки для кортежу
cmin	Ідентифікатор команди, асоційованої з транзакцією вставки для кортежу
хmах	Ідентифікатор транзакції видалення для кортежу. Для видимих (не видалених) кортежів дорівнює нулю
сmах	Ідентифікатор команди, асоційованої з транзакцією видалення для кортежу. За аналогією з xmax дорівнює нулю для видимих кортежів
ctid	Ідентифікатор, що описує фізичне місцезнаходження кортежу в БД. Поле ctid містить пару чисел: номер блоку та індекс кортежу в блоці

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

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

DROP TABLE tablename;

3.4 Обмеження

P o stgreSQL має декілька варіантів обмеження даних (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. Це обмеження складається з таких секцій:
  

1– REFERENCES таблиця [(поле)]. Вхідні значення обмежуваного поля порівнюються із значеннями іншого поля в заданій таблиці. Якщо збіги відсутні, то команда INSERT

або UPDATE завершується невдачею. Якщо параметр поле не вказаний, то перевірка виконується за первинним ключем. Обмеження REFERENCES подібне до обмеження таблиці FOREIGN KEY, яке описане в наступному пункті цього підрозділу. Дійсно, між цими обмеженнями є багато спільного;

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

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

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

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

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

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

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

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

CREATE TABLE gazpr

(kod_g integer PRIMARY KEY CHECK (kod_g > 0),

naz_g CHARACTER(80) NOT NULL, kilnyt INTEGER CHECK (kilnyt > 1), diam INTEGER);

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'gazpr_pkey' for

table 'gazpr'

Тут створюється поле kod_g типу integer, для якого встановлюються обмеження PRIMARY KEY і CHECK. Обмеження PRIMARY KEY також має на увазі обмеження NOT NULL і UNIQUE і зумовлює автоматичне створення індекса gazpr_pkey для обмежуваного поля. Обмеження CHECK гарантує, що значення поля kod_g завжди буде додатнє, а поля kilnyt – більше за одиницю. Це означає, що будь-які спроби вставки або оновлення в таблиці gazpr записів, поле kod_g яких менше або дорівнює 0, завершуються невдачею.

Таблиця gazpr також містить текстове поле naz_g, для якого встановлено обмеження NOT NULL. Воно забороняє мати в таблиці записи, поле naz_g яких містить NULL. Інакше кажучи, це поле обов’язкове для заповнення.

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

Нижче створюються дві таблиці: gazpr і oblik з трьома обмеженнями:

CREATE TABLE gazpr

(kod_g INTEGER, naz_g CHARACTER(80), kilnyt INTEGER, diam INTEGER, CONSTRAINT aaa PRIMARY KEY (kod_g), CONSTRAINT bbb CHECK (kilnyt IS NOT NULL AND diam IS NOT NULL);

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pkey' for table 'gazpr'

CREATE TABLE oblik (kod_g INTEGER, kod_p INTEGER, data DATE, kilk

REAL, CONSTRAINT ccc FOREIGN KEY (kod_g) REFERENCES gazpr(kod_g)

ON DELETE CASCADE ON UPDATE CASCADE);

NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)

Перше обмеження, aaa належить до типу PRIMARY KEY і встановлюється для поля kod_g. Воно практично не відрізняється від обмеження PRIMARY KEY для поля, оскільки список у даному прикладі складається всього з одного поля.

Обмеження bbb гарантує, що поля kilnyt і diam не буде містити псевдозначення NULL.

Нарешті, обмеження ccc таблиці oblik за допомогою конструкцій FOREIGN KEY і REFERENCES гарантує, що значення поля kod_g таблиці oblik зустрічаються і в полі kod_g таблиці gazpr. Більш того, оскільки в секціях ON DELETE і ON ACTION зустрічається ключове слово CASCADE, будь-які модифікації поля kod_g в таблиці gazpr спричинять каскадні зміни записів у таблиці oblik, а при видаленні записів з таблиці gazpr будуть видалені відповідні записи таблиці oblik.

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

SET CONSTRAINTS { ALL | режим [.... ] } { DEFERRED | IMMEDIATE }, де:

• 
  ALL означає, що вказаний режим повинен стосуватися всіх обмежень;
  
• 
  режим – ім’я обмеження, для якого встановлюється режим перевірки;
  
• 
  DEFERRED – перевірка обмежень (або конкретного обмеження) вікладається до момента фіксації транзакції, тобто до виконання команди COMMIT;
  
• 
  IMMEDIATE – всі або конкретне обмеження перевіряються наприкінці кожної команди. Команда SET CONSTRAINTS задає режим перевірки для всіх обмежень або одного обмеження в поточному транзакційному блоці. Існує два режими перевірки: негайна (IMMEDIATE) і відкладена (DEFERRED) перевірка обмежень. У режимі IMMEDIATE всі обмеження перевіряються після виконання кожної команди транзакції, а в режимі DEFERRED обмеження перевіряються лише після виконання команди COMMIT.
  

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

Нижченаведена команда вибирає режим негайної перевірки всіх обмежень.

SET CONSTRAINTS ALL IMMEDIATE;

3.5 Послідовності

P os t gr eSQL є об’єктно-реляційною СУБД, що дозволило включити в неї ряд нестандартних розширень SQL. Частинацих розширень пов’язана з автоматизацією часто вживаних операцій з базами даних, це, зокрема, послідовності і тригери.

Послідовність (sequence) являє собою послідовність чисел. Значення послідовності мають тип integer, тому її значення повинні лежати в межах від 2 147 483 647 до -2 147 483 647. Як правило, її використовують у обмеженні DEFAULT для надання унікальних значень полям таблиць. Ці значення автоматично змінюються шляхом виклику функції nextval().

Послідовність створюється командою:

CREATE SEQUENCE послідовність

[ INCREMENT приріст] [ MINVALUE мінімум ] [ MAXVALUE максимум ]

[ START початок ] [ CACHE кеш ] [ CYCLE ] де:

• 
  послідовність – ім’я послідовності, це єдиний обов’язковий параметр;
  
• 
  INCREMENT приріст – нарощення поточного значення послідовності, це число може бути від’ємним або додатнім. За замовчуванням приріст дорівнює 1.
  
• 
  MINVALUE мінімумум – мінімально допустиме значення. Спроба зменшити поточне значення нижче за заданий мінімум спричинить помилку або циклічний перехід до максимального значення (якщо послідовність створювалася з ключовим словом CYCLE). За замовчуванням мінімальне значення дорівнює 1;
  
• 
  MAXVALUE максимум – максимально допустиме значення послідовності. Спроба перевищити заданий максимум спричинить помилку або циклічний перехід до мінімального значення (при CYCLE). За замовчуванням максимальне значення дорівнює 2 147 483 647;
  
• 
  START початок – початкове значення послідовності, це будь-яке ціле число в інтервалі між мінімальним і максимальним значеннями;
  
• 
  CACHE кеш – забезпечує можливість попереднього обчислення і зберігання значень послідовності в оперативній пам’яті. Кешування прискорює доступ до тих послідовностей, які часто використовуються;
  
• 
  CYCLE – повторне, циклічне використання послідовності. Досягши нижнього або верхнього порога, послідовність продовжує генерувати нові значення, тоді вона переходить до мінімального значення при зростанні послідовності або до максимального – при убуванні. Оскільки в цих випадках значення послідовності повторюються, то вони не будуть унікальними.
  

У нижченаведеному прикладі створюється послідовність з ім’ям ship, яка починається із значення 0 і збільшується на 1 до тих пір, поки не досягне максимального значення. Ключове слово CYCLE не вказане, тому ця послідовністьнабуває лише унікальних значень.

До послідовності також можна звернутися командою SELECT, як до таблиці, хоча така можливість використовується відносно рідко, наприклад, під час випробування або перевірки послідовності. При складанні запиту до послідовності в списку вибірки вказуються їїатрибути, перелічені в таблиці 3.1.

Таблиця 3.1. Атрибути послідовності

Атрибут	Тип	Примітка
sequence_name	name	Ім’я послідовності
last_value	integer	Поточне значення
increment_by	integer	Нарощення
max_value	integer	Максимальне значення
min_value	integer	Мінімальне значення
cache_value	integer	Об’єм кеша, байт
log_cnt	integer
is_cycled	"char"
is_called	"char"

Нижче показано запит до послідовності ship.

SELECT last_value, increment_by FROM ship;

Як правило, всі операції з послідовностями виконуються за допомогою таких трьох спеціальних функцій PostgreSQL

• 
  nextval( ‘послідовність’) – нарощує поточне і повертає нове значення;
  
• 
  currval(‘послідовність’) – повертає поточне значення;
  
• 
  setval (‘послідовність’, n) – змінює поточне значення на число n.
  

Нижче в прикладі виводиться пара чергових значень послідовності ship.

Послідовності найчастіше використовуються для задавання значень за замовчуванням у полях таблиць. Нехай таблицю pidpr створено командою:

CREATE TABLE pidpr(kod_p integer PRIMARY KEY DEFAULT nextval('ship'), naz_p character(50), misto character(30));

Тут поле kod_p має обмеження DEFAULT, функція nextval('ship') якого буде надавати кожного разу нарощені на одиницю значення за замовчуванням, вироблені послідовністю ship. Це поле має також обмеження PRIMARY KEY, яке, нагадаємо, означає ще й обмеження UNIQUE, тому в нього неможливо буде помилково занести неунікальні значення.

Тепер команда вставки даних у таблицю pidpr може виглядати, наприклад, так:

INSERT INTO pidpr (naz_p, misto) VALUES

('Уренгой – Помари – Ужгород', 'Полтава');

Під час виконання команди код підпримства kod_p буде занесений у таблицю автоматично.

Змінити поточне значення послідовності дозволяє функція setval(), нижче показано, що значення послідовності ship змінюється на 1010, а далі нарощується на 1, тому дорівнює 1011:

SELECT setval('ship', 1010);

Видалення послідовності забезпечує команда SQL DROP SEQUENCE, вона видаляє одну або декілька послідовностей одночасно. Ця команда має такий вигляд:

DROP SEQUENCE перелік_послідовностей

Приклад видалення послідовності ship: DROP SEQUENCE ship;

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

SELECT p.relname, a.adsrc FROM pg_class p JOIN pg_attrdef a ON

(p.relfilenode = a.adrelid) WHERE a.adsrc