1

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:

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

enter image description here

  1. Is there any future problem issue if I change directly as above (no.2) Thank you!
Azlina T
  • 176
  • 2
  • 17

1 Answers1

1

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

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 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. – Azlina T Jan 19 '22 at 05:07
  • 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. – Laurenz Albe Jan 19 '22 at 06:56
  • 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! – Azlina T Jan 20 '22 at 01:45