Пошук уроків, статей та іншого контенту
Створіть стовпці, що автоматично генерують послідовні ідентифікатори для нових рядків.
Identity column — це стовпець, значення якого PostgreSQL генерує автоматично під час додавання нового рядка.
Найчастіше identity columns використовують для первинних ключів:
id integer GENERATED ALWAYS AS IDENTITYЯкщо не вказати id під час INSERT, PostgreSQL сам створить послідовне числове значення:
1, 2, 3, ...Identity column зручна тим, що:
не потрібно вручну рахувати новий ідентифікатор;
кожен новий рядок отримує власне значення;
генерація ідентифікатора належить самій таблиці;
стовпець можна використовувати разом із PRIMARY KEY.
Створімо таблицю товарів:
CREATE TABLE products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
price numeric(10, 2) NOT NULL
);Тут:
id — ідентифікатор товару;
integer — тип даних ідентифікатора;
GENERATED ALWAYS AS IDENTITY — PostgreSQL генерує значення автоматично;
PRIMARY KEY — значення id повинні бути унікальними та не можуть бути NULL;
name і price — звичайні стовпці таблиці.
Під час вставлення нового товару не потрібно вказувати id:
INSERT INTO products (name, price)
VALUES ('Keyboard', 2500.00);
INSERT INTO products (name, price)
VALUES ('Mouse', 800.00);Перевіримо результат:
SELECT id, name, price
FROM products
ORDER BY id;Результат буде приблизно таким:
id | name | price
----+---------+---------
1 | Keyboard| 2500.00
2 | Mouse | 800.00Якщо потрібно отримати створений ідентифікатор одразу після INSERT, використовуйте RETURNING:
INSERT INTO products (name, price)
VALUES ('Monitor', 12000.00)
RETURNING id, name, price;PostgreSQL поверне доданий рядок разом із автоматично згенерованим id.
PostgreSQL підтримує два основні режими identity columns.
GENERATED ALWAYSCREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);У цьому режимі PostgreSQL завжди очікує, що значення буде згенеровано автоматично.
Звичайне додавання рядка працює так:
INSERT INTO customers (name)
VALUES ('Olena');Спроба самостійно передати значення id завершиться помилкою:
INSERT INTO customers (id, name)
VALUES (100, 'Andrii');Цей режим підходить, коли ідентифікатори завжди повинні генеруватися базою даних.
GENERATED BY DEFAULTCREATE TABLE orders (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
description text NOT NULL
);У цьому режимі PostgreSQL генерує id, якщо його не передали. Але за потреби можна вказати власне значення:
INSERT INTO orders (description)
VALUES ('First order');
INSERT INTO orders (id, description)
VALUES (100, 'Imported order');Для більшості звичайних таблиць достатньо GENERATED ALWAYS. Варіант BY DEFAULT може бути корисним під час імпорту даних, коли частину ідентифікаторів потрібно зберегти без змін.
Наступний приклад можна виконати в PostgreSQL послідовно:
DROP TABLE IF EXISTS tasks;
CREATE TABLE tasks (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
completed boolean NOT NULL DEFAULT false
);
INSERT INTO tasks (title)
VALUES
('Read PostgreSQL documentation'),
('Create a practice table'),
('Write the first query');
SELECT id, title, completed
FROM tasks
ORDER BY id;
INSERT INTO tasks (title, completed)
VALUES ('Complete the lesson', true)
RETURNING id, title, completed;У таблиці tasks ідентифікатори створюються автоматично, а для completed використовується значення false, якщо його не вказати.
Identity column гарантує унікальні значення, але не гарантує відсутність пропусків.
Наприклад:
INSERT INTO tasks (title)
VALUES ('Temporary task');
DELETE FROM tasks
WHERE title = 'Temporary task';
INSERT INTO tasks (title)
VALUES ('Another task');Після видалення першого рядка новий рядок не обов’язково отримає його id. Результат може містити такі ідентифікатори:
1, 2, 3, 5Пропуски також можуть з’явитися через скасовані транзакції або невдалі операції вставлення.
Це нормальна поведінка. Ідентифікатор потрібен для розрізнення рядків, а не для ведення безперервної нумерації без пропусків.
Identity column можна додати до вже створеного стовпця:
ALTER TABLE products
ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;Це спрацює лише за умови, що стовпець id відповідає вимогам PostgreSQL для identity column і його наявні значення не конфліктують із майбутніми значеннями.
На практиці identity column часто створюють одразу під час CREATE TABLE, щоб структура таблиці від початку була зрозумілою.
Identity column та PRIMARY KEY виконують різні функції:
identity column генерує значення;
PRIMARY KEY перевіряє, що значення унікальне та не є NULL.
Зазвичай їх використовують разом:
CREATE TABLE users (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL
);Сам identity column не робить стовпець первинним ключем автоматично. Якщо стовпець має бути ідентифікатором таблиці, явно додайте PRIMARY KEY.
INSERTНепотрібно писати так:
INSERT INTO products (id, name, price)
VALUES (1, 'Keyboard', 2500.00);Для GENERATED ALWAYS це призведе до помилки. Правильний варіант:
INSERT INTO products (name, price)
VALUES ('Keyboard', 2500.00);Ідентифікатори не призначені для нумерації рахунків, квитанцій або інших послідовностей, де кожен номер має використовуватися рівно один раз.
Не покладайтеся на те, що після 10 завжди буде 11. Гарантується унікальність, а не відсутність пропусків.
PRIMARY KEYТакий стовпець генерує значення, але не захищає таблицю від дублювання, якщо його не зробити ключем:
id integer GENERATED ALWAYS AS IDENTITYЯкщо id має бути основним ідентифікатором рядка, використовуйте:
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEYNULL замість того, щоб пропустити стовпецьПід час вставлення нового рядка краще не включати identity column до списку стовпців:
INSERT INTO products (name, price)
VALUES ('Keyboard', 2500.00);Це правильніше, ніж намагатися передати для id значення вручну.
Identity column автоматично генерує числові ідентифікатори.
Для створення використовується синтаксис GENERATED ... AS IDENTITY.
GENERATED ALWAYS забороняє ручне задання значення у звичайному INSERT.
GENERATED BY DEFAULT дозволяє передати власне значення за потреби.
Identity column часто поєднують із PRIMARY KEY.
Під час INSERT identity column зазвичай не вказують.
Пропуски в ідентифікаторах є нормальною поведінкою.
За допомогою RETURNING можна одразу отримати створений ідентифікатор.