ERROR: relation “…_seq” does not exist

August 9, 2023 1 Min Read

PostgreSQL has three auto increment column types:

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
);