ERROR: relation "schema.TableName_Id_seq" does not exist - when creating table in a new database

Question

I'm having an issue where I used pgAdmin4's GUI to create a SQL table, and I want to use to generated CREATE TABLE script to create this same table in another database.

When I run the CREATE TABLE script generated by pgAdmin4 in my new database, I get the following error:

ERROR: relation "schema.TableName_Id_seq" does not exist

So, it appears that the issue is with my auto-incrementing id column that I created as type SERIAL.

The CREATE TABLE script as provided by pgAdmin4:

-- Table: myschema.TableName

-- DROP TABLE myschema."TableName";

CREATE TABLE myschema."TableName"
(
    "Id" integer NOT NULL DEFAULT nextval('myschema."TableName_Id_seq"'::regclass),
    /* Other columns here */
    CONSTRAINT "TableName_pkey" PRIMARY KEY ("Id")
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE myschema."TableName"
    OWNER to JoshuaSchlichting;

Why can't the CREATE TABLE script be used in another database? The relation "schema.TableName_Id_seq" didn't exist in the original database prior to be creating that table. What's happening that is different?

Answer 1

The DDL script provided by pgAdmin4 is not complete. When the table was created, there was an implicit creation of a sequence because of the SERIAL type being select for the Id column.

You can find this newly create sequence with pgAdmin4. To do this, go to

• -> your server
• -> your database
• -> your schema
• -> Sequences
• -> Right click TableName_Id_seq
• -> choose "Create script"

This reveals the script used to create this sequence. In this instance, the following was revealed:

-- SEQUENCE: myschema.TableName

-- DROP SEQUENCE myschema."TableName";

CREATE SEQUENCE myschema."TableName"
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 2147483647
    CACHE 1;

The use of the CREATE SEQUENCE script can be avoided by changing the line of code used to create the Id column in the CREATE TABLE script. Example below:

original line: "Id" integer NOT NULL DEFAULT nextval('myschema."TableName_Id_seq"'::regclass),

changed to: "Id" SERIAL NOT NULL,