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

Question

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

my local psql version is 10.2
server psql version is 9.6.8

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.

Did you inspect the dump for the sequence? Are there other errors in the output? — clemens
– clemens, Commented Mar 19, 2018 at 5:45
@clemens i'm not sure if my dump file has sequence or not. Could you please help? — Saly
– Saly, Commented Mar 19, 2018 at 6:58

Saly · Accepted Answer · 2018-03-20 02:05:30Z

14

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;

answered Mar 20, 2018 at 2:05

Saly

1,5263 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gagalago Over a year ago

the problem was that you generate the dump on postgres 10 and restore it on a lower version where the syntax "AS data_type" is not avalable

Saly Over a year ago

@gagalago thank you for your command. I will be careful about version next time.

Fırat Küçük · Accepted Answer · 2022-07-06 07:37:24Z

7

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

edited Jul 6, 2022 at 7:37

answered Jun 19, 2020 at 6:21

Fırat Küçük

6,0732 gold badges55 silver badges57 bronze badges

1 Comment

Amon Over a year ago

Oh ffs this was the answer for me, thank you brother

clemens · Accepted Answer · 2018-03-19 08:09:28Z

4

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.

answered Mar 19, 2018 at 8:09

clemens

17.9k12 gold badges52 silver badges71 bronze badges

2 Comments

Saly Over a year ago

In my dump file has CREATE SEQUENCE table_id_seq ... but it's still error.

clemens Over a year ago

@Saly: So you should look into the output if there are any errors. You may add SET client_min_messages TO NOTICEto increase the log level.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related