Автоувеличение id в PostgreSQL

#sql #postgresql #insert

Таблица содержит два столбца: 
id | description

Как сделать так, что при добавлении новой строки id пользователя будет наращиваться
сам? То есть 
INSERT INTO users DEFAULT VALUES;

даст (1, ), (2, ) и тд.    

Ответы

Ответ 1

Надо сделать id типом serial или big serial:

serial    4 bytes autoincrementing integer       1 to 2147483647
bigserial 8 bytes large autoincrementing integer 1 to 9223372036854775807

CREATE TABLE users (
    id          SERIAL,
    description TEXT
);


Ответ 2

Дополню ответ @John Brown, так как типы SERIAL и BIGSERIAL появились в Postgres не
так давно.
Еще один способ создать автоинкрементное поле заключается в том, чтобы связать его
с последовательностью (SEQUENCE), наподобие того, как это делается в Оракле:
CREATE SEQUENCE table_id_seq;

ALTER TABLE table 
    ALTER COLUMN id 
        SET DEFAULT NEXTVAL('table_id_seq');


Ответ 3

Начиная с PostgreSQL 10 был реализован синтаксис создания автоматически генерируемых
последовательностей соответствующий современным стандартам SQL.

сreate table foo (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
);


Этот синтаксис является стандартом, и такой запрос будет переносим между, например,
PostgreSQL, DB2, Oracle.

Если чуть углубиться внутрь - этот синтаксис postgresql опирается на уже давно существующие
 свои sequence. Так, этим запросом был неявно создан новый sequence и дополнительно
привязан к полю таблицы, так чтобы его не получилось случайно удалить.

Упомянутые в других ответах smallserial, serial и bigserial - это тоже ненастоящие
типы данных и как таковых их не существует. Например, вы не сможете привести другой
тип данных к serial. Это простой синтаксический сахар вокруг создания sequence и назначения
default для поля. Так, таблица вида

create table foo (id serial);


На самом деле является эквивалентом для:

CREATE TABLE public.foo (
  id integer NOT NULL
);

CREATE SEQUENCE public.foo_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE public.foo_id_seq OWNED BY public.foo.id;

ALTER TABLE ONLY public.foo ALTER COLUMN id SET DEFAULT nextval('public.foo_id_seq'::regclass);


И именно так его видно через pg_dump, например.



Поэтому фактически ответ: если вы хотите автоматически генерируемый счётчик в postgresql,
безопасный для конкурентного доступа - вам нужны sequence. А способов его объявить,
как это часто бывает в SQL, есть несколько разных.

Если версия базы позволяет, наверное будет лучше уже использовать стандартный SQL
синтаксис GENERATED BY DEFAULT AS IDENTITY.


Ответ 4

завести последовательность и повесить тригер на вставку (на предвставку)


Ответ 5

CREATE SEQUENCE схема.таблица_id_seq MINVALUE=0 NO MAXVALUE CYCLE;
ALTER TABLE схема.таблица 
ALTER COLUMN id 
SET DEFAULT nextval('схема.таблица_id_seq');
ALTER SEQUENCE схема.таблица_id_seq OWNED BY схема.таблица_id_seq;