CODEWITHSUNDEEP
postgresqlpostgresql-9.6pg-dumppostgresql-10
Saly
Saly

Reputation: 1496

PostgreSql : ERROR: relation "sequence" does not exist while restoring from dump file

I get the following error while restoring database from dump file on server:

ERROR: relation "table_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('table_id_seq', 362, true);

Here is my dump command:

pg_dump -U username -h localhost db_name > filename.sql

Here is my restore command on server:

psql -U username -h localhost db_name < filename.sql

Please help, Thanks.

Upvotes: 9

Views: 23164

Answers (3)

Fırat K&#252;&#231;&#252;k
Fırat K&#252;&#231;&#252;k

Reputation: 6003

In my case, the sequence checking is case-sensitive. That's why I was getting the relation error. So maybe it helps some people like me who end up desperately here. I've used a double-quote inside the single quotation mark in the SQL statement.

SELECT nextval('"USER_ID_seq"');

There're some examples in the official documentation: https://www.postgresql.org/docs/9.1/functions-sequence.html

Upvotes: 6

Saly
Saly

Reputation: 1496

After I got information from @clemens and make some research I found that, in my dump file on section CREATE SEQUENCE table_id_seq has a statement AS integer that why when I restored into new database it did not create the nextval() for the sequence. If I remove the statement AS integer from the CREATE SEQUENCE section it works find.

In my dump file:

CREATE SEQUENCE table_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

Remove AS integer from dump file

CREATE SEQUENCE table_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

Upvotes: 14

clemens
clemens

Reputation: 17721

You can open the dump file with any text editor (Notepad, Vim, etc.). Search for table_id_seq. You should find a statement like

CREATE SEQUENCE table_id_seq ...

If it is missing then there is something strange with your dump. You might fix that by adding

CREATE SEQUENCE table_id_seq;

immediately in front of the statement

SELECT pg_catalog.setval('table_id_seq', 362, true);

from the error message.

But this is just a hack. You were supposed to find out why the dump made that mistake. But that requires more information.

Upvotes: 4

Related Questions