ERROR: relation “…_seq” does not exist

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

Leave a comment

Your email address will not be published. Required fields are marked *