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?

Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_upper_case_table_or_column_names — , May 04 '20 at 20:30
@a_horse_with_no_name I agree! The script above is generated by pgAdmin4 and/or PostgreSQL itself, not me! — Joshua Schlichting, May 04 '20 at 20:36
The script only reflects what has been done before. At one point someone ran the CREATE TABLE with the dreaded quoted identifiers. — , May 04 '20 at 20:38
You know what, I noticed from your link that those double quotes are likely there because of the capital letters in the table name. I didn't realize that this is what was happening. I will avoid that going forward now, thanks for sharing, @a_horse_with_no_name. — Joshua Schlichting, May 04 '20 at 20:38
@a_horse_with_no_name The table was initially created via the pgAdmin4 GUI, and not via a manually written `CREATE TABLE` script. EDIT: I just created a new table and tested that, with pgAdmin4. Sure enough, if you use capital letters when creating the table, pgAdmin4 is implicitly placing double quotes around it when it creates that table. Good to know! — Joshua Schlichting, May 04 '20 at 20:39

score 2 · Accepted Answer · answered May 04 '20 at 20:00

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,

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

1 Answers1