Когда мы пытаемся создать таблицу из графического интерфейса pgAdmin 4, то в большинстве случаев мы хотим иметь столбец с уникальным идентификатором строки, который создаётся автоматически и увеличивается на единицу при каждой новой добавленной записи самостоятельно. Как это сделать?
Решение
Предлагаю создать простую таблицу с двумя столбцами:
- Первый называется «id«
- Второй называется «qwe«
И теперь нам нужно добраться до наших таблиц:
Подключаем сервер в интерфейсе pgAdmin 4
Открываем нужную базу данных
Открываем раздел «Schemas»
Открываем раздел «public»
Пролистываем до раздела «Tables»
Выделяем раздел «Tables» левой кнопкой мыши
Нажимаем правую клавишу мыши над разделом «Tables»
В выпадающем списке выбираем пункт «Create» (Создать)
Выпадет ещё один список с одним элементом «Table…» (Таблица…)
Откроется диалоговое окно «CreateTable»
Предлагаю назвать таблицу «R77»
Переходим на вкладку «Columns»
Дважды нажимаем на кнопку добавления нового столбца в таблицу в интерфейсе pgAdmin 4. Кнопка оформлена в виде значка «+» (ПЛЮС)
Вводим имена для столбцов сверху вниз.
- «id«
- «qwe«
Теперь необходимо заполнить типы данных для каждого столбца нашей таблицы
Для «id» мы выбираем «bigint»
Для «qwe» мы выбираем «text»
Мы выбрали тип данных «bigint«. Это целое число в большом диапазоне от -9223372036854775808 до 9223372036854775807. Оно занимает размер в 8 байт. Вы также можете выбрать «smallint» (от -32768 до +32767) или «integer» (от -2147483648 до +2147483647). Выбор зависит от задачи. В нашем примере можно использовать любой. Мы просто тренируемся.
Теперь нам нужно настроить конфигурацию столбца «id«. Для этого нажимаем на символ карандаша слева. При наведении всплывает информация «Edit row»
Нам нужно указать, что столбец «id» в таблице «R77» является первичным ключом. Для этого переключаем тумблер «Primary key?«, чтобы он подсветился синим цветом.
Теперь нужно перейти на вкладку «Constraints» для нашего столбца «id» и настроить для него «Ограничения».
В этом блоке нам нужно включить «Type» в значение «Identity«, чтобы раскрыть дополнительные настройки столбца.
Нам раскрывается список с полями:
- Identity
- Increment
- Start
- Minimum
- Maximum
- Cache
- Cycled
В нашем случае мы выбираем поле «Identity» в значение «ALWAYS«.
В поле «Increment» ставим значение «1«. Значение будет увеличиваться на единицу с каждой строкой автоматически.
В поле «Start» ставим значение «1» т. к. нас интересуют только целые положительные значения в автоинкременте.
Остальные поля нас не интересуют.
После ввода всех необходимых данных нам нужно сохранить изменения. Для этого нажимаем на синюю кнопку «Save» со значком дискеты.
С этого момента pgAdmin 4 формирует SQL-запрос и посылает его в СУБД PostgreSQL, которая установлена на сервере (или виртуальной машине)
— Table: public.R77
— DROP TABLE IF EXISTS public.»R77″;
CREATE TABLE IF NOT EXISTS public.«R77»
(
id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
qwe text COLLATE pg_catalog.«default»,
CONSTRAINT «R77_pkey» PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.«R77»
OWNER to postgres;
В вашем случае таблицы будут называться по другому. Это просто пример запроса.
Тест работы автоинкремента в таблице
Сейчас таблица пуста.
Давайте добавим данные в три ячейки столбца «qwe»
Обратите внимание, что графический интерфейс pgAdmin 4 сейчас нам показывает ячейки столбца «id» со значениями «null«.
Это нужно понимать как «НЕСОХРАНЁННЫЕ ДАННЫЕ». То есть мы пока не сохранили введённые изменения в таблицу.
Сохраняем результат. Для этого нажимаем на значёк «цилиндра с дискетой» или клавишу F6.
После этого pgAdmin 4 отправит 5 SQL-запросов на сервер СУБД.
BEGIN;
INSERT INTO public.«R77» (qwe) VALUES (‘qqq’::text) returning id;
INSERT INTO public.«R77» (qwe) VALUES (‘www’::text) returning id;
INSERT INTO public.«R77» (qwe) VALUES (‘eee’::text) returning id;
COMMIT;
И когда операция будет завершена, тогда мы увидим в графическом интерфейсе наши заветные автоувеличенные идентификаторы строк, которые будут являться первичными ключами.
Задача выполнена! Автоинкремент работает
Дополнительная информация
В PostgreSQL существуют специальные типы данных:
- bigserial (псевдоним serial8) — восьмибайтное целое с автоувеличением
- serial (псевдоним serial4) — четырёхбайтное целое с автоувеличением
- smallserial (serial2) — двухбайтное целое с автоувеличением
Автоинкремент напрямую зависит от команды вставки записи в таблицу — INSERT.
Если по каким-то причинам, процесс вставки записи в таблицу нарушается, то команда INSERT всё равно блокирует идентификатор автоинкремента под себя и откатывает состояние. Это значит, что следующая вставка НИКОГДА НЕ получит прежний идентификатор от неудачной вставки.
Такая проблема (особенность) есть практически во всех СУБД. Тут вопрос в параллельном доступе.
Процесс А собирается вставить запись и получает ID. получил, атомарно. В это время Процесс Б то же вставляет запись — получил ID.
Процесс А наконец переходит к вставке и что-то идёт не так, он откатывает изменение. Но откатывать ID нельзя потому что Процесс Б уже взял следующий номер.
Исправить это можно только заблокировав все вставки в таблицу пока Процесс А не завершится. Скорость вставок упадёт очень сильно так как остальные вставки будут стоять в очереди. На такое ни одна СУБД не пойдет, потому что это приведёт к смертельному замедлению работы СУБД. В этом нет никакого смысла.
Проще всего делать проверку существования записи по условию и только потом производить вставку, чтобы не «откусывать» лишние инкрементные значения.
Информационные ссылки
Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org
Официальный сайт СУБД PostgreSQL — https://www.postgresql.org
Стандарт «PostgreSQL» — Раздел «Числовые типы» — https://postgrespro.ru/docs/postgresql/14/datatype-numeric
Стандарт «PostgreSQL» — Раздел «columns» (Столбцы) — https://postgrespro.ru/docs/postgresql/14/infoschema-columns
