Статья основана на четвертом видео из 30 тем курса SQL c 0 от Аристова Евгения. Ссылки на видео на платформах RUTUBE и VK видео.
В статье про первичные и внешние ключи мы касались темы последовательностей. Сегодня разберемся в этом подробнее.
Основные моменты
Sequence (последовательность) – автоматическая уникальная нумерация. Её можно создавать самостоятельно или использовать готовый вариант.
Для создания можно использовать поле типа SERIAL (синтаксический сахар, под капотом это integer 4 байта + NOT NULL + создание последовательности + создание DEFAULT (по умолчанию) значения у поля – подробнее рассмотрим на 11 занятии), которое берёт следующее значение из созданной последовательности.
Serial
Рассмотрим подробнее SERIAL:
Диапазон значений с 1 до 2.147.483.647.
Если 2 млрд мало, то можно использовать тип BIGSERIAL (big integer 8 байт).
Важно! Если не планируется огромное количество записей (больше 2 млрд.), то рекомендуется использовать SERIAL для экономии места на индексах и внешних ключах. Сокращение поля до SMALLSERIAL обычно не имеет смысла, так как впоследствии данные выравниваются кратно 32 битам (4 байта).
Использует сквозную нумерацию, поэтому при отмене транзакции (rollback) счётчик не возвращается в исходное значение. Так появляются “дырки” в последовательности индексов.
Создается вручную в двух случаях:
- Если нужна сквозная нумерация в разных таблицах. Например, при инвентаризации все объекты имеют уникальный номер, но могут быть записаны в разные таблицы
- При использовании master-master репликации (например, при наличии двух активных серверов). Если встретятся два одинаковых id, то репликация остановится с ошибкой. Варианты решения: в случае двух серверов использовать четные id на одном сервере и нечетные на другом, при использовании более двух серверов – сдвиг в id (на первом 1, 4, 7; на втором 2, 5, 8 и т.д.). Для трёх серверов, соответственно, 1,4,7 и тд
Про создание из документации
Есть вариант создания UNLOGGED sequence. Он работает быстрее, но при рестарте сервера счетчик будет перезаписан и переинициализирован.
При помощи параметра CACHE можно указать, сколько значений будет сразу резервироваться. Это используется для того, чтобы данные, вставленные подряд в одной транзакции имели последовательные номера. Второй плюс – не нужно запрашивать каждый номер – немного ускоряем работу. Но есть и минус – если не доиспользовали зарезервированные номера – они пропадут и не будут использованы в последовательности.
Параметр NO CYCLE позволяет не начинать последовательность заново после максимального значения – генерация остановится и не сможем в автоматическом режиме сгенерировать значение счетчика – только ручной режим.
Рекомендуется создавать через конструкцию IF NOT EXISTS – чтобы в больших скриптах получить предупреждение (WARNING), а не ошибку (ERROR), при которой вся транзакция будет отменена (ROLLBACK).
Пример создания последовательности our_sequence с типом данных integer (то есть возможным количеством значений до 2 млрд.), шагом 2 и минимальным (стартовым) значением 10.
CREATE SEQUENCE IF NOT EXISTS our_sequence
AS integer
INCREMENT 2
MINVALUE 10
Функции для sequence
- Nextval – возвращает текущее значение и переключает значение на следующее
- Currval – возвращает текущее значению
- Setval – устанавливает указанное значение
Работа с sequence
Создадим таблицу test с двумя полями – i (тип serial) и t (тип text) и добавим в неё три значения. При добавлении значений указываем только поле t, а i записывается автоматически. С помощью SELECT просматриваем, что лежит в нашей таблице
CREATE TABLE test (i serial, t text);
INSERT INTO test(t) VALUES ('value1');
INSERT INTO test(t) VALUES ('value2');
INSERT INTO test(t) VALUES ('value3');
SELECT * FROM test;
Далее используем функцию:
SELECT nextval('test_i_seq');
Имя test_i_seq генерируется автоматически для поля типа serial. Формат – имятаблицы_имяполя_seq (seq от sequence)
Следующее значение – 4 – выведено и счетчик увеличен
Проверим это:
SELECT currval('test_i_seq');
Добавим ещё одну строчку, ей должен присвоиться значение 5
INSERT INTO test(t) VALUES ('value4');
SELECT * FROM test;
Далее установим значение последовательности на 1 и добавим строчку. Ей будет присвоено значение 2, так как присваивается следующее за текущим.
SELECT setval('test_i_seq',1);
INSERT INTO test(t) VALUES ('value 5');
SELECT * FROM test;
Получившийся код целиком:
CREATE TABLE test (i serial, t text);
INSERT INTO test(t) VALUES ('value1');
INSERT INTO test(t) VALUES ('value2');
INSERT INTO test(t) VALUES ('value3');
SELECT * FROM test;
SELECT nextval('test_i_seq');
SELECT currval('test_i_seq');
INSERT INTO test(t) VALUES ('value4');
SELECT * FROM test;
SELECT setval('test_i_seq',1);
INSERT INTO test(t) VALUES ('value 5');
SELECT * FROM test;Необходимо отметить, что с 10 версии PostgreSQL доступна генерация не последовательности, а типа IDENTITY. В целом это тоже самое, подробнее разберём в 11 теме.
Презентация к статье здесь.
Пятая из 30 тем будет скоро доступна. Если вы хотите быстрее получить доступ – присоединяйтесь к онлайн группе, ссылка доступна в описании курса.
Добавить комментарий