PostgreSQL Antipatterns: уникальные идентификаторы
Достаточно часто у разработчика возникает потребность формировать для записей таблицы PostgreSQL некие уникальные идентификаторы — как при вставке записей, так и при их чтении.
Таблица счетчиков
Казалось бы — чего проще? Заводим отдельную табличку, в ней — запись со счетчиком. Надо получить новый идентификатор — читаем оттуда, чтобы записать новое значение — делаем UPDATE …
Так делать не надо! Потому что завтра же вам придется решать проблемы:
Объект SEQUENCE
Чтобы получить следующий ID из последовательности, достаточно воспользоваться функцией nextval :
Иногда необходимо получить сразу несколько ID — для потоковой записи через COPY, например. Использовать для этого setval(currval() + N) — в корне неправильно! По той простой причине, что между вызовами «внутренней» ( currval ) и «внешней» ( setval ) функций конкурирующая транзакция могла изменить текущее значение последовательности. Корректный способ — вызвать nextval нужное количество раз:
Псевдотип serial
Однако, поскольку работа с последовательностью нетранзакционна, если идентификатор из нее получала rollback’нувшаяся транзакция, то в сохраненных записях таблицы последовательность ID окажется «дырявой».
GENERATED-столбцы
Да, чтобы вставить конкретное значение «поперек» такого столбца, придется приложить дополнительные усилия с помощью OVERRIDING SYSTEM VALUE :
Генерируемый UUID
Все хорошо, пока вы работаете в рамках одного экземпляра БД. Но когда их несколько, адекватного способа синхронизации последовательностей не существует (впрочем, это не мешает «неадекватно» их синхронизировать, если очень хочется). Тут на помощь приходит тип UUID и функции генерации значений для него. Я обычно использую uuid_generate_v4() как наиболее «случайную».
Скрытые системные поля
tableoid/ctid
Иногда при выборке записей из таблицы требуется как-то адресоваться к конкретной «физической» записи, или узнать, из какой конкретной секции была получена та или иная запись при обращении к «родительской» таблице при использовании наследования.
В этом случае нам помогут скрытые системные поля, присутствующие в каждой записи:
Вплоть до PostgreSQL 11 существовала возможность объявить при создании таблицы атрибут WITH OIDS :
При вставке записи в такую таблицу генерируемое значение возвращается сразу с результатом запроса:
Такое поле невидимо при «обычном» запросе таблицы:
Его, как и остальные системные поля надо запрашивать в явном виде:
«Честное» время clock_timestamp
Иногда при длительном выполнении запроса или процедуры хочется привязать к записи «текущее» время. Неудача ждет того, кто попытается для этого использовать функцию now() — она возвратит одно и то же значение в рамках всей транзакции.
Чтобы получить «вот прямо текущее» время, существует функция clock_timestamp() (и еще пучок ее собратьев). Чем отличается поведение этих функций можно увидеть на примере простого запроса:
Иллюстрированный самоучитель по PostgreSQL
Числовые типы
Тип serial
Хотя тип serial не относится к числу стандартных типов, он часто используется при создании в таблице полей-идентификаторов, содержащих уникальное значение для каждой записи. В типе serial объединены функциональные возможности 4-байтового типа integer, индекса и последовательности. В листинге 3.24 тип serial генерирует уникальный идентификатор для каждой записи в таблице auto_identified.
В листинге 3.25 та же задача решается при помощи поля типа integer, функции nextval() и последовательности (последовательности описаны в главе7). На момент написания книги эти два способа были функционально тождественными.
Листинг 3.24. Использование типа serial.
Листинг 3.25. Решение задачи «вручную».
Внимание
После удаления таблицы последовательность, созданная для типов serial, автоматически не удаляется. При удалении таблиц, содержащих поля типа serial, эти последовательности должны удаляться отдельно.
Дата и время
Следующая категория типов является удобным средством для хранения даты и времени в универсальной структуре SQL. При этом программист избавляется от хлопот, связанных с форматом хранения (как, например, при хранении этих данных в символьных строках). В PostgreSQL все вычисления с датой и временем производятся но юлианскому календарю. Продолжительность года считается равной 365.24 дня, что обеспечивает правильное представление любой даты с 4713 года до нашей эры и далеко в будущее.
В PostgreSQL поддерживаются все типы даты и времени, определенные в стандарте SQL92 (табл. 3.14), а также некоторые вспомогательные типы PostgreSQL, помогающие решить проблемы с представлением часовых поясов в SQL92.
Таблица 3.14. Типы даты и времени.
Как установить автоинкрементный первичный ключ PostgreSQL?
Могут быть случаи, когда вы создаете и поддерживаете таблицы в PostgreSQL, когда вам нужны определенные значения для столбца, который создается по запросу. Это особенно верно для столбцов «id», которые действуют как первичный ключ таблицы. К счастью, псевдотип SERIAL позволяет сделать удобными целочисленные серии с автоматическим приращением. Серия — это тип объекта базы данных в PostgreSQL, который производит серию индексов или целых чисел. Последовательность PostgreSQL создает строку различных целых чисел, что делает ее пригодной для использования в качестве первичного ключа при создании новой таблицы. Мы продемонстрируем вам, что такое автоинкрементное меню в PostgreSQL, и в этом руководстве мы будем использовать псевдотип SERIAL.
Синтаксис
Общий синтаксис для создания первичного ключа с автоинкрементом следующий:
>> CREATE TABLE table_name ( id SERIAL ) ;
Давайте теперь посмотрим на объявление CREATE TABLE более подробно:
Чтобы понять концепцию автоинкремента, убедитесь, что PostgreSQL смонтирован и настроен в вашей системе, прежде чем продолжить работу с иллюстрациями в этом руководстве. Откройте оболочку командной строки PostgreSQL с рабочего стола. Добавьте имя сервера, на котором вы хотите работать, в противном случае оставьте значение по умолчанию. Напишите имя базы данных, которая находится на вашем сервере, на котором вы хотите работать. Если вы не хотите его менять, оставьте значение по умолчанию. Мы будем использовать «тестовую» базу данных, поэтому добавили ее. Вы также можете работать с портом по умолчанию 5432, но вы также можете его изменить. В конце концов, вы должны указать имя пользователя для выбранной вами базы данных. Оставьте значение по умолчанию, если вы не хотите его менять. Введите свой пароль для выбранного имени пользователя и нажмите «Enter» на клавиатуре, чтобы начать использовать командную оболочку.
Использование серийного ключевого слова в качестве типа данных
Когда мы создаем таблицу, мы обычно не добавляем ключевое слово SERIAL в поле основного столбца. Это означает, что мы должны добавить значения в столбец первичного ключа при использовании оператора INSERT. Но когда мы используем ключевое слово SERIAL в нашем запросе при создании таблицы, нам не нужно добавлять значения основного столбца при вставке значений. Давайте взглянем на это.
Пример 1
Создайте таблицу «Тест» с двумя столбцами «id» и «name». Столбец «id» был определен как столбец первичного ключа, так как его тип данных — SERIAL. С другой стороны, столбец «имя» определяется как тип данных TEXT NOT NULL. Попробуйте выполнить команду ниже, чтобы создать таблицу, и таблица будет создана эффективно, как показано на изображении ниже.
>> CREATE TABLE Test ( id SERIAL PRIMARY KEY, name TEXT NOT NULL ) ;
Вставим несколько значений в столбец «имя» вновь созданной таблицы «ТЕСТ». Мы не будем добавлять никакого значения в столбец «id». Вы можете видеть, что значения были успешно вставлены с помощью команды INSERT, как указано ниже.
Пришло время проверить записи таблицы «Тест». Попробуйте выполнить приведенную ниже инструкцию SELECT в командной оболочке.
Из выходных данных ниже вы можете заметить, что столбец «id» автоматически содержит некоторые значения, хотя мы не добавляли никаких значений из команды INSERT из-за типа данных SERIAL, который мы указали для столбца «id». Вот как тип данных SERIAL работает сам по себе.
Пример 2
Другой способ проверить значение столбца типа данных SERIAL — использовать ключевое слово RETURNING в команде INSERT. Объявление ниже создает новую строку в таблице «Тест» и дает значение для поля «id»:
>> INSERT INTO Test ( name ) VALUES ( ‘Hassam’ ) RETURNING id ;
Проверив записи таблицы «Тест» с помощью запроса SELECT, мы получили следующий результат, как показано на изображении. Пятая запись оперативно добавлена в таблицу.
Пример 3
Альтернативная версия указанного выше запроса на вставку использует ключевое слово DEFAULT. Мы будем использовать имя столбца «id» в команде INSERT, а в разделе VALUES мы дадим ему ключевое слово DEFAULT в качестве его значения. Приведенный ниже запрос будет работать так же после выполнения.
Давайте еще раз проверим таблицу, используя запрос SELECT следующим образом:
Вы можете видеть из вывода ниже, новое значение было добавлено, а столбец «id» был увеличен по умолчанию.
Id serial sql что это
��������:
CREATE SEQUENCE «public».»seq»
INCREMENT 1 MINVALUE 1
MAXVALUE 9223372036854775807 START 35
CACHE 1;
������: ������, ��������
���������: 228
| ChameLe0n | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ��� ������. � ���� ������ ����� ����� ����������� ��� � serial �� ������:) ��������: ������: ���� |
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
