Открыть страницу поиска
Привет, Я DocuDroid!
Открыть страницу ИИ Помощника
Оценка ИИ поиска
Спасибо за оценку нашего ИИ поиска!
Мы будем признательны, если вы поделитесь своими впечатлениями, чтобы мы могли улучшить наш ИИ поиск для вас и других читателей.
GitHub

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

Андрей Аксенов

Последовательность (sequence) — это объект в схеме базы данных, который генерирует уникальные целые числа по возрастанию или убыванию. Последовательности обычно используются для генерации уникальных идентификаторов для строк таблицы. Когда столбец объявлен с последовательным (serial) типом — например, SERIAL или BIGSERIAL — автоматически создается последовательность, которая ассоциируется с этим столбцом для генерации возрастающих значений.

Обзор

Greengage DB предоставляет следующие команды для создания, изменения и удаления последовательностей:

  • CREATE SEQUENCE

  • ALTER SEQUENCE

  • DROP SEQUENCE

Синтаксис команды CREATE SEQUENCE описан ниже:

CREATE [ TEMP ] SEQUENCE <sequence_name>
    [ INCREMENT [BY] <value> ]
    [ MINVALUE <minvalue> | NO MINVALUE ]
    [ MAXVALUE <maxvalue> | NO MAXVALUE ]
    [ START [WITH] <start> ]
    [ CACHE <cache> ]
    [ [NO] CYCLE ]
    [ OWNED BY { <table>.<column> | NONE } ]

Описания выражений команды CREATE SEQUENCE:

  • CREATE SEQUENCE <sequence_name>

    Создает новую последовательность с указанным именем. Имя последовательности может включать имя схемы (<schema_name>.<sequence_name>).

  • TEMP

    Если указано, объект последовательности создается только для текущей сессии.

  • INCREMENT [BY]

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

  • MINVALUE <minvalue>

    Указывает минимальное значение, которое может сгенерировать последовательность.

  • MAXVALUE <maxvalue>

    Указывает максимальное значение для последовательности.

  • START [WITH] <start>

    Указывает значение, с которого начинается последовательность.

  • CACHE <cache>

    Указывает количество значений последовательности, которые будут получены и сохранены в памяти для более быстрого доступа.

  • [NO] CYCLE

    Позволяет последовательности возвращаться к началу при достижении maxvalue (по возрастанию) или minvalue (по убыванию). Смотрите раздел Переполнение последовательности ниже.

  • OWNED BY

    Связывает последовательность со столбцом таблицы. Если этот столбец или таблица удаляются, последовательность также автоматически удаляется.

После создания последовательности можно использовать следующие функции для работы с ней:

ПРИМЕЧАНИЕ

Функции PostgreSQL currval() и lastval() для работы с последовательностями не поддерживаются в Greengage DB.

Создание последовательности

Предварительные требования

Для выполнения команд, описанных в следующих разделах, подключитесь к мастер-хосту Greengage DB с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql. Затем создайте новую базу данных и подключитесь к ней:

CREATE DATABASE marketplace;
\c marketplace

Команда CREATE SEQUENCE ниже создает последовательность с указанным именем и начальным значением:

CREATE SEQUENCE customer_id_seq START 101;

Последовательность customer_id_seq начинается с 101 и по умолчанию увеличивается на 1, генерируя уникальные числовые значения при каждом вызове.

Использование последовательностей в DML-командах

Получение нового значения последовательности

В этом разделе показано, как использовать функцию nextval() для генерации уникальных идентификаторов для строк таблицы. Функция принимает имя последовательности в качестве аргумента.

Создайте таблицу:

CREATE TABLE customers
(
    customer_id BIGINT,
    name        TEXT,
    email       TEXT
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (customer_id);

Для вставки данных используйте команду INSERT INTO. Для генерации значений идентификатора клиента в функцию nextval() передается имя последовательности:

INSERT INTO customers (customer_id, name, email)
VALUES (nextval('customer_id_seq'), 'Andrew Fuller', 'andrew@example.com'),
       (nextval('customer_id_seq'), 'Michael Suyama', 'michael@testmail.com'),
       (nextval('customer_id_seq'), 'Laura Callahan', 'laura@example.io'),
       (nextval('customer_id_seq'), 'Nancy Davolio', 'nancy@samplemail.com'),
       (nextval('customer_id_seq'), 'Steven Buchanan', 'steven@fastmail.net');

Используйте команду SELECT, чтобы проверить данные, добавленные в таблицу:

SELECT *
FROM customers
ORDER BY customer_id;

Вывод показывает, что у каждого клиента есть уникальный ID начиная с 101:

 customer_id |      name       |        email
-------------+-----------------+----------------------
         101 | Laura Callahan  | laura@example.io
         102 | Nancy Davolio   | nancy@samplemail.com
         103 | Michael Suyama  | michael@testmail.com
         104 | Andrew Fuller   | andrew@example.com
         105 | Steven Buchanan | steven@fastmail.net
(5 rows)

nextval() и откат транзакций

Операция nextval() никогда не откатывается. Как только значение получено, оно считается использованным — даже если транзакция, вызвавшая nextval(), завершилась неудачно. В результате неудачные транзакции могут приводить к пропускам в последовательности назначаемых значений.

Следующий запрос пытается вставить новую запись о клиенте, но затем откатывает транзакцию, отменяя операцию INSERT:

BEGIN;
INSERT INTO customers (customer_id, name, email)
VALUES (nextval('customer_id_seq'), 'Margaret Peacock', 'margaret@example.com');
ROLLBACK;

Используйте SELECT nextval(), чтобы проверить текущее значение последовательности:

SELECT nextval('customer_id_seq');

Возвращаемое значение должно быть 107, что указывает на то, что последовательность увеличивается, даже если операция INSERT была отменена:

 nextval
---------
     107

Установка текущего значения последовательности

Вы можете использовать функцию setval() для установки текущего значения последовательности. Эта функция также принимает необязательный параметр is_called. Если этот параметр установлен в true (по умолчанию), следующий вызов nextval() продвинет последовательность, прежде чем вернуть значение. Если is_called — false, nextval() вернет последнее значение.

Следующая команда устанавливает текущее значение последовательности customer_id_seq в 200:

SELECT setval('customer_id_seq', 200);

Чтобы вставить новую строку в таблицу, используйте команду INSERT INTO:

INSERT INTO customers (customer_id, name, email)
VALUES (nextval('customer_id_seq'), 'Margaret Peacock', 'margaret@example.com');

Затем получите данные из таблицы customers:

SELECT *
FROM customers
ORDER BY customer_id;

Результат должен показать, что customer_id для новой строки равен 201:

 customer_id |       name       |        email
-------------+------------------+----------------------
         101 | Laura Callahan   | laura@example.io
         102 | Nancy Davolio    | nancy@samplemail.com
         103 | Michael Suyama   | michael@testmail.com
         104 | Andrew Fuller    | andrew@example.com
         105 | Steven Buchanan  | steven@fastmail.net
         201 | Margaret Peacock | margaret@example.com

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

По умолчанию последовательность не перезапускается после достижения максимального/минимального значения. Когда достигается максимальное или минимальное значение (предел типа данных или значение параметра <maxvalue>/<minvalue>), любой вызов nextval() приводит к ошибке. Чтобы разрешить последовательности возвращаться к значению, заданному выражением START [WITH], используйте параметр CYCLE.

Создайте новую последовательность с MAXVALUE, установленным в 5:

CREATE SEQUENCE customer_id_seq_small MAXVALUE 5;

Создайте таблицу:

CREATE TABLE customers_with_small_sequence
(
    customer_id BIGINT,
    name        TEXT,
    email       TEXT
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (customer_id);

Вставьте пять значений в таблицу:

INSERT INTO customers_with_small_sequence (customer_id, name, email)
VALUES (nextval('customer_id_seq_small'), 'Andrew Fuller', 'andrew@example.com'),
       (nextval('customer_id_seq_small'), 'Michael Suyama', 'michael@testmail.com'),
       (nextval('customer_id_seq_small'), 'Laura Callahan', 'laura@example.io'),
       (nextval('customer_id_seq_small'), 'Nancy Davolio', 'nancy@samplemail.com'),
       (nextval('customer_id_seq_small'), 'Steven Buchanan', 'steven@fastmail.net');

Затем попробуйте вставить новое значение:

INSERT INTO customers_with_small_sequence (customer_id, name, email)
VALUES (nextval('customer_id_seq_small'), 'Margaret Peacock', 'margaret@example.com');

Возвращается следующая ошибка:

ERROR:  nextval: reached maximum value of sequence "customer_id_seq_small" (5)

Чтобы разрешить последовательности переходить к начальному значению при достижении максимального, включите опцию CYCLE, используя команду ALTER SEQUENCE:

ALTER SEQUENCE customer_id_seq_small CYCLE;

Запрос INSERT должен выполняться без ошибок:

INSERT INTO customers_with_small_sequence (customer_id, name, email)
VALUES (nextval('customer_id_seq_small'), 'Margaret Peacock', 'margaret@example.com');

Получите данные из таблицы customers_with_small_sequence:

SELECT *
FROM customers_with_small_sequence
ORDER BY customer_id;

Результат показывает, что существует два клиента с ID, равным 1:

 customer_id |       name       |        email
-------------+------------------+----------------------
           1 | Margaret Peacock | margaret@example.com
           1 | Andrew Fuller    | andrew@example.com
           2 | Michael Suyama   | michael@testmail.com
           3 | Steven Buchanan  | steven@fastmail.net
           4 | Nancy Davolio    | nancy@samplemail.com
           5 | Laura Callahan   | laura@example.io
(6 rows)

Использование последовательностей в определении таблицы

Значение столбца по умолчанию

Вы можете использовать последовательность в команде CREATE TABLE, например:

CREATE TABLE customers_with_sequence
(
    customer_id BIGINT DEFAULT nextval('customer_id_seq'),
    name        TEXT,
    email       TEXT
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (customer_id);

Если вы вставляете строки без указания значений customer_id, Greengage DB автоматически получает следующее значение из последовательности customer_id_seq:

INSERT INTO customers_with_sequence (name, email)
VALUES ('Andrew Fuller', 'andrew@example.com'),
       ('Michael Suyama', 'michael@testmail.com'),
       ('Laura Callahan', 'laura@example.io'),
       ('Nancy Davolio', 'nancy@samplemail.com'),
       ('Steven Buchanan', 'steven@fastmail.net');

Получите данные из таблицы:

SELECT *
FROM customers_with_sequence
ORDER BY customer_id;

Результат должен выглядеть так:

 customer_id |      name       |        email
-------------+-----------------+----------------------
         202 | Andrew Fuller   | andrew@example.com
         203 | Michael Suyama  | michael@testmail.com
         204 | Laura Callahan  | laura@example.io
         205 | Nancy Davolio   | nancy@samplemail.com
         206 | Steven Buchanan | steven@fastmail.net
(5 rows)

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

Объявление столбца последовательного типа (SMALLSERIAL, SERIAL или BIGSERIAL) неявно создает последовательность для использования в этом столбце таблицы. Последовательность получает имя по шаблону <table_name>_<column_name>_seq и может использоваться в DML-командах так же, как и любая последовательность, созданная вручную. Вы можете узнать больше о последовательных типах в соответствующем разделе документации PostgreSQL: Serial Types.

В приведенном ниже примере столбец customer_id имеет тип SERIAL:

CREATE TABLE customers_with_serial
(
    customer_id SERIAL,
    name        TEXT,
    email       TEXT
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (customer_id);

При вставке строк Greengage DB автоматически получает следующее значение из неявно созданной последовательности:

INSERT INTO customers_with_serial (name, email)
VALUES ('Andrew Fuller', 'andrew@example.com'),
       ('Michael Suyama', 'michael@testmail.com'),
       ('Laura Callahan', 'laura@example.io'),
       ('Nancy Davolio', 'nancy@samplemail.com'),
       ('Steven Buchanan', 'steven@fastmail.net');

Получите данные из таблицы:

SELECT *
FROM customers_with_serial
ORDER BY customer_id;

Результат должен выглядеть так:

 customer_id |      name       |        email
-------------+-----------------+----------------------
           1 | Andrew Fuller   | andrew@example.com
           2 | Michael Suyama  | michael@testmail.com
           3 | Laura Callahan  | laura@example.io
           4 | Nancy Davolio   | nancy@samplemail.com
           5 | Steven Buchanan | steven@fastmail.net

Изменение последовательностей

Команда ALTER SEQUENCE изменяет атрибуты существующей последовательности.

Чтобы перезапустить последовательность customer_id_seq начиная с 1001, используйте команду:

ALTER SEQUENCE customer_id_seq RESTART WITH 1001;

Свяжите последовательность с определенным столбцом таблицы:

ALTER SEQUENCE customer_id_seq OWNED BY customers.customer_id;

Добавьте новые данные в таблицу customers:

INSERT INTO customers (customer_id, name, email)
VALUES (nextval('customer_id_seq'), 'Robert King', 'robert@demo.org'),
       (nextval('customer_id_seq'), 'Janet Leverling', 'janet@businessmail.com');

Затем получите данные из таблицы:

SELECT *
FROM customers
ORDER BY customer_id;

Результат показывает, что идентификаторы новых клиентов начинаются с 1001:

 customer_id |       name       |         email
-------------+------------------+------------------------
         101 | Laura Callahan   | laura@example.io
         102 | Nancy Davolio    | nancy@samplemail.com
         103 | Michael Suyama   | michael@testmail.com
         104 | Andrew Fuller    | andrew@example.com
         105 | Steven Buchanan  | steven@fastmail.net
         201 | Margaret Peacock | margaret@example.com
        1001 | Robert King      | robert@demo.org
        1002 | Janet Leverling  | janet@businessmail.com
(8 rows)

Просмотр информации о последовательностях

В этом разделе показано, как получить информацию о последовательностях.

  • Чтобы получить информацию о последовательности, используйте команду SELECT:

    SELECT *
FROM customer_id_seq;

    Результат включает параметры последовательности, такие как ее начальное значение, минимальное и максимальное значения:

      sequence_name  | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
-----------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 customer_id_seq |       1002 |         101 |            1 | 9223372036854775807 |         1 |           1 |      31 | f         | t

  • Чтобы получить список всех последовательностей, используйте метакоманду psql \ds:

    \ds

    Результат выглядит следующим образом:

                              List of relations
 Schema |                 Name                  |   Type   |  Owner
--------+---------------------------------------+----------+---------
 public | customer_id_seq                       | sequence | gpadmin
 public | customer_id_seq_small                 | sequence | gpadmin
 public | customers_with_serial_customer_id_seq | sequence | gpadmin

    Результат включает автоматически созданную последовательность customers_with_serial_customer_id_seq для столбца customer_id с типом SERIAL.

  • Вы можете получить информацию о последовательностях из представления information_schema.sequences:

    SELECT sequence_schema,
       sequence_name,
       data_type,
       minimum_value,
       maximum_value,
       increment,
       cycle_option
FROM information_schema.sequences;

    Результат выглядит следующим образом:

     sequence_schema |             sequence_name             | data_type | minimum_value |    maximum_value    | increment | cycle_option
-----------------+---------------------------------------+-----------+---------------+---------------------+-----------+--------------
 public          | customer_id_seq                       | bigint    | 1             | 9223372036854775807 | 1         | NO
 public          | customer_id_seq_small                 | bigint    | 1             | 5                   | 1         | YES
 public          | customers_with_serial_customer_id_seq | bigint    | 1             | 9223372036854775807 | 1         | NO

Удаление последовательностей

Чтобы удалить последовательность, используйте команду DROP SEQUENCE:

DROP SEQUENCE customer_id_seq_small;

Чтобы удалить последовательность вместе со связанными объектами, например, таблицами, которые ее используют, используйте параметр CASCADE:

DROP SEQUENCE customer_id_seq CASCADE;
Предыдущая
Партиционирование
Следующая
Индексы