The table contains two columns:
id | description
How to make it so that when a new line is added, the user id will grow by itself? That is
INSERT INTO users DEFAULT VALUES;
Answer 1, authority 100%
We need to make the id type serial or 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 );
Answer 2, authority 67%
I’ll complement the answer @John Brown as the
BIGSERIAL types appeared in Postgres is not that long ago.
Another way to create an auto-incrementing field is to associate it with a SEQUENCE, like in Oracle:
CREATE SEQUENCE table_id_seq; ALTER TABLE table ALTER COLUMN id SET DEFAULT NEXTVAL ('table_id_seq');
Answer 3, authority 58%
Starting with PostgreSQL 10, the syntax for creating automatically generated sequences has been implemented in accordance with modern SQL standards.
create table foo ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY );
This syntax is standard and such a query will be portable between, for example, PostgreSQL, DB2, Oracle.
Going a little deeper, this
postgresql syntax builds on its long-standing
sequence . Thus, this query implicitly created a new
sequence and additionally tied it to a table field so that it would not be accidentally deleted.
bigserial mentioned in other answers are also not real data types and do not exist as such. For example, you cannot cast another datatype to serial. It’s simple syntactic sugar around creating a
sequence and assigning a
default to a field. So, a table like
create table foo (id serial);
Is actually equivalent for:
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);
And this is how it is seen through
pg_dump , for example.
So the actual answer is that if you want an auto-generated counter in postgresql that is concurrently safe, you need a
sequence . And there are several different ways to declare it, as is often the case in SQL.
If the version of the database allows, it will probably be better to use the standard SQL syntax
GENERATED BY DEFAULT AS IDENTITY .
CREATE SEQUENCE schema.id_seq_table MINVALUE = 0 NO MAXVALUE CYCLE; ALTER TABLE schema.table ALTER COLUMN id SET DEFAULT nextval ('schema.id_seq_table'); ALTER SEQUENCE schema.id_seq_table OWNED BY schema.id_seq_table;