Последовательности в PostgreSQL

Материал из AOW

Перейти к: навигация, поиск

В СУБД PostgreSQL для получения последовательных уникальных значений применяются последовательности (SEQUENCE [1]).

Общий синтаксис создания последовательности:

 CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
   [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
   [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
   [ OWNED BY { table.column | NONE } ]

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

   CREATE SEQUENCE content_tb_id_seq
     INCREMENT BY 1
     NO MAXVALUE
     NO MINVALUE
     CACHE 1;
   CREATE TABLE content_tb (
     id integer DEFAULT nextval('content_tb_id_seq') NOT NULL,
     ...,
     title character varying(512) NOT NULL,
     shortbody character varying(4096) NOT NULL,
     fullbody text NOT NULL,
     ...
   );

В данном примере необязательный пункт INCREMENT [ BY ] был задан 1. Этот пункт управляет, какое число добавлять к последнему элементу последовательности для получения нового. Значение по умолчанию – 1.

Пункты NO MINVALUE и NO MAXVALUE снимают ограничения на максимальное и минимальное значения элемента последовательности.

Необязательный пункт CACHE был установлен в 1. Данный пункт следит за количеством следующих предопределенных элементов последовательности. Значение по умолчанию – 1.

Также PostgreSQL поддерживает тип данных SERIAL и BIGSERIAL. Он автоматически создает последовательность. Например:

   CREATE TABLE content_tb (
     id SERIAL,
     ...,
     title character varying(512) NOT NULL,
     body text NOT NULL,
     ...
   );

автоматически транслируется в:

  CREATE SEQUENCE content_tb_id_seq;
  CREATE TABLE content_tb (
     id integer DEFAULT nextval('content_tb_id_seq') NOT NULL,
     ...,
     title character varying(512) NOT NULL,
     body text NOT NULL,
     ...
   );

Автоматически созданная последовательность имеет имя вида <таблица>_<поле_serial>_seq, где таблица и поле_serial - это соответственно имена таблицы и поля с типом SERIAL.

Функции для работы с последовательностями:

  • currval(regclass) – возвращает результат последнего вызова nextval() для заданной последовательности
  • lastval()– возвращает результат последнего вызова nextval()
  • nextval(regclass) – генерирует новый элемент заданной последовательности и возвращает его
  • setval(regclass, bigint[, boolean]) – изменяет текущее значение последовательности и возвращает его. Последний необязательный параметр is_called указывает на необходимость поледующего вызова nextval()

Подводные камни:

  1. При удалении записей в нумерации возникают разрывы. Решение проблемы: не требует вмешательства.
  2. Возможна ошибочная вставка записи с ещё не использованным номером в SEQUENCE. Например:
INSERT INTO content_tb (title, body) VALUES (‘Заголовок 1’, 'Тело 1');
INSERT INTO content_tb (id, title, body) VALUES (3, ‘Заголовок 0’, 'Тело 0');
INSERT INTO content_tb (title, body) VALUES (‘Заголовок 2’, 'Тело 2');
INSERT INTO content_tb (title, body) VALUES (‘Заголовок 3’, 'Тело 3');
INSERT INTO content_tb (title, body) VALUES (‘Заголовок 4’, 'Тело 4');

Последний запрос вызовет ошибку, т.к. автоматически-сгенерированное значение первичного ключа уже будет присутствовать в таблице.

Решение проблемы:

  • Узнать значение следующего элемента последовательности:
SET search_path = news, pg_catalog, public;
SELECT nextval('content_tb_id_seq'::regclass);

Файл:Pg-seq-1.PNG

  • Узнать максимальное значение индекса в таблице:
SELECT max(id) FROM news.content_tb;

Файл:Pg-seq-2.PNG

  • Увеличить значение «счетчика» в случае необходимости:
SET search_path = news, pg_catalog, public;
SELECT setval('content_tb_id_seq', (SELECT max(id) FROM news.content_tb));

Файл:Pg-seq-3.PNG

  • Проверить результат своих действий:
SET search_path = news, pg_catalog, public;
SELECT nextval('content_tb_id_seq'::regclass);

Файл:Pg-seq-4.PNG


См.также

Личные инструменты

Разработка веб-сайтов, автоматизация.
По всем вопросам обращайтесь по телефонам:

+7 495 640 29 90
http://artofweb.ru