Alter/change default value for sequence on postgreSQL

Question

I would like to alter my sequence value in database. On top of that, I tried to get the sequence info by below query

SELECT PG_GET_SERIAL_SEQUENCE('test_thing', 'id');

which is it return to NULL.

enter image description here

Further check in Postgres (using HeidiSQL) db for table test_thing, I can see my primary key default value is:

(nextval('test_thing_thing_seq_id'::regclass)

which I believe is causing the result to return to NULL and would like to change/alter the value as below

(nextval('test_thing_thing_id_seq'::regclass)

The questions are:

How I'm supposed to change the value without dropping the table and maintain the data
Or can I just change directly from here

Is there any future problem issue if I change directly as above (no.2) Thank you!

Laurenz Albe · Accepted Answer · 2022-01-17 06:47:33Z

2

First, pg_get_serial_sequence will return the sequence name only if you have added a dependency between the sequence and the column; it does not look at theDEFAULT clause You can add a dependency with

ALTER SEQUENCE test_thing_thing_id_seq
   OWNED BY test_thing(id);

You can change the sequence value with

SELECT setval('test_thing_thing_id_seq', 42);

That can probably also be done with a GUI client like HeidiSQL, but I don't know because I don't use GUI clients

Changing the sequence value can cause problems if the new value is lower than the maximum of id in the table: future INSERTs could result in primary key constraint violation errors

answered Jan 17, 2022 at 6:47

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

Azlina T Over a year ago

Yes you are right, I'm currently changing the sequence value as my app return those error ' primary key constraint violation errors '. Which is why I would like to change the (nextval('test_thing_thing_seq_id'::regclass) to (nextval('test_thing_thing_id_seq'::regclass). I'm not sure if the sequence of ...seq_id is the culprit on why when I run the query SELECT PG_GET_SERIAL_SEQUENCE('test_thing', 'id'); and it return to null.

Laurenz Albe Over a year ago

If that function returns NULL, no sequence has a dependency on the table column. In that case, you will have to look at the default clause of the column definition to figure out which sequence to modify.

Azlina T Over a year ago

Ok understood. Means that I just need to query SELECT nextval('epil_files_seq_id'); to obtain the next sequence value and boom! got the result. Thanks for the help mate!

Collectives™ on Stack Overflow

Alter/change default value for sequence on postgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related