PostgreSQL типи доменів і перелічення (Enums): Забезпечення цілісності даних - javascript.org.ua

Ласкаво просимо до третьої публікації в нашій серії про розширені можливості PostgreSQL. В попередніх публікаціях ми досліджували, як проектувати схему бази даних і забезпечувати цілісність даних за допомогою обмежень. Будуючи на цій основі, сьогодні ми поглибимося в два потужних інструменти PostgreSQL: типи доменів та перелічення (enums).

Коли мова йде про роботу з базами даних, забезпечення цілісності даних є критичним. Одним зі способів досягнення цього в PostgreSQL є використання типів доменів та перелічень (enums). Вони допомагають визначити обмеження і обмежити типи даних, які можуть бути вставлені в таблицю. Давайте розглянемо, як це працює і чому ці інструменти такі корисні.

Користувацькі типи доменів

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

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

Ось приклад того, як ми можемо створити та використовувати користувацький тип домену для поштового коду США:

CREATE DOMAIN us_postal_code AS TEXT   
CONSTRAINT postal_code_format CHECK (  
 VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$'  
);

У наведеному коді:

Ми визначаємо домен us_postal_code як тип TEXT.
Обмеження забезпечує, щоб поштовий код відповідав формату п’ятизначного коду (наприклад, 12345) або дев’ятизначного коду з дефісом (наприклад, 12345-6789).

Тепер створимо таблицю і використаємо цей тип домену:

CREATE TABLE addresses (  
 city TEXT,  
 address TEXT,  
 post_code us_postal_code  
);

Ось кілька операцій insert:

INSERT INTO addresses (city, address, post_code)  
VALUES ('LA', 'Main-Street', '12345');  
-- УСПІШНО  
INSERT INTO addresses (city, address, post_code)  
VALUES ('LA', 'Main-Street', '1234');  
-- ПОМИЛКА: значення для домену us_postal_code порушує перевірку обмеження "postal_code_format"

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

Перелічення (Enums) у PostgreSQL

ENUM — це обмежений набір заздалегідь визначених значень. Перелічення (enums) є відмінним способом зберігати поле, яке має фіксований набір можливих значень. Наприклад, якщо ви керуєте статусом замовлення в системі електронної комерції, можливо, вам захочеться визначити набір можливих статусів, таких як {NEW, PAID, IN_DELIVERY, DELIVERED, CANCELED}.

Переваги перелічень (Enums):

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

Ось як можна створити перелічення (enum) у PostgreSQL:

CREATE TYPE mood AS ENUM ('HAPPY', 'SAD', 'NEUTRAL');

Тепер ви можете використовувати тип перелічення (enum) у таблиці:

CREATE TABLE clients (  
 name TEXT,  
 current_mood MOOD  
);

Вставка даних у таблицю проста:

INSERT INTO clients(name, current_mood)  
VALUES ('Bruce', 'HAPPY'),  
 ('Ann', 'SAD'),  
 ('Bob', 'NEUTRAL');

Якщо ви спробуєте вставити значення, якого немає в переліченні (enum), отримаєте помилку:

INSERT INTO clients(name, current_mood)  
VALUES ('Jack', 'Obsessed');  
-- ПОМИЛКА: невірне значення для enum mood: "Obsessed"

Порядок перелічень (Enum Ordering)

Однією з цікавих функцій перелічень є їх порядок. Порядок значень у переліченні є порядком, у якому вони визначені.
Цей порядок має велике значення при виконанні запитів, таких як ORDER BY:

SELECT * FROM clients ORDER BY current_mood;  

-- ВИХІД  
+-----+------------+  
|name |current_mood|  
+-----+------------+  
|Bruce|HAPPY      |  
|Ann  |SAD        |  
|Bob  |NEUTRAL    |  
+-----+------------+

Результат буде відсортований на основі внутрішнього порядку значень перелічення (enum) (HAPPY = 1, SAD = 2, NEUTRAL = 3).

Додавання нових значень до Enum

Ви можете розширити існуючий тип enum, додавши нові значення. Нові значення за замовчуванням додаються в кінець списку, але ви також можете вказати, де саме ви хочете їх вставити:

ALTER TYPE mood ADD VALUE 'EXCITED';  

INSERT INTO clients(name, current_mood)  
VALUES ('Jamie', 'EXCITED');  

SELECT distinct * FROM clients ORDER BY current_mood;  

+-----+------------+  
|name |current_mood|  
+-----+------------+  
|Bruce|HAPPY      |  
|Ann  |SAD        |  
|Bob  |NEUTRAL    |  
|Jamie|EXCITED    |  
+-----+------------+

Якщо ви хочете контролювати порядок значень, можна вказати, чи має нове значення з’явитися до чи після існуючих:

ALTER TYPE mood ADD VALUE 'OBSESSED' BEFORE 'SAD';  
ALTER TYPE mood ADD VALUE 'AFRAID' AFTER 'HAPPY';

Додаткові функції PostgreSQL

Корисною функцією для розробників, які працюють з PostgreSQL, є PG_TYPEOF(), яка повертає внутрішній тип даних для заданого значення. Це може бути корисним для налагодження або розуміння перетворення даних у ваших запитах.

PG_TYPEOF('11:13:59.77091+05'::time); -- Повертає: time without time zone  
PG_TYPEOF(12.2999); -- Повертає: numeric

Висновок

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

Попередні публікації:

Якщо вам сподобалась ця публікація, ставте лайк і підписуйтесь, щоб не пропустити наступні публікації. Успіхів у програмуванні!

Перекладено з: PostgreSQL Domain Types and Enums: Ensuring Data Integrity