PostgreSQL has three auto increment column types:
- SMALLSERIAL
- SERIAL
- BIGSERIAL
The pdAdmin 4 user interface unfortunately generates the wrong CREATE script for existing tables:
CREATE TABLE IF NOT EXISTS public.test (
id integer NOT NULL DEFAULT nextval('test_id_seq'::regclass)
)
If we execute it, we get the error message:
ERROR: relation “test_id_seq” does not exist
LINE 3: id integer NOT NULL DEFAULT nextval(‘test_id_seq’::regcl…
The correct syntax which auto generates the sequence too, is:
CREATE TABLE public.test (
id SERIAL PRIMARY KEY
);
For more information see Creating tables with PostgreSQL