How to change start value of sequence in Postgres

Question

I have a table which was populated with data from another environment. When I try to create a new entry it tells me:

ERROR: duplicate key value violates unique constraint "chart_of_account_dimension_config_pkey"
Detail: Key (id)=(1) already exists.

I tried resetting the starting value of the sequence to an higher value by:

select setval(chart_of_account_dimension_id_seq1, 2810, true)

But it tells me

column "chart_of_account_dimension_config_id_seq1" does not exist

I tried to run following query, and actually there is no such sequence. But dBeaver tells me such a sequence exists. Edit: Why postgres thinks that chart_of_account_dimension_config_id_seq1 is a column name whereas in reality it is a sequence name.

Answer 1

If the query parser sees an identifier like that in that place it tries to treat it as a column.

So you need to do:

select setval('chart_of_account_dimension_id_seq1'::regclass, 2810, true)

That will look up the text name of the sequence and give its underlying identifier.

If you check the output of \dt you should see a similar thing with the DEFAULT for the column using it.