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 SERIAL and 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.

The smallserial , serial and 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 .

Answer 4

Answer 5

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;