Search code examples
sqlpostgresqlheidisql

Alter/change default value for sequence on postgreSQL

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!
Solution

  • 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