Create and Alter Sequence in PostgreSQL

Create and Alter Sequence in PostgreSQL

To activate autoincrement property in PRIMARY KEY field for a table having huge records present in postgresql. We need to create sequence and assign it to the corresponding table.

Creating a sequence :

1. Assume that we have a table called [test_table] with an unique column called [id]
   
2. Generate sequence
   
3. run command > create <sequence name> , i.e. create test_table_id_sequence;
   
4. After the sequence’s already created, we can call NEXTVAL(‘<Sequence name>’) to generate a new value automatically.
5. Link the sequence to the unique column
6. ALTER TABLE <table name> ALTER COLUMN <column name> SET DEFAULT NEXTVAL(<created sequence name>);
   => ALTER TABLE test_table ALTER COLUMN id SET DEFAULT NEXTVAL('test_table_id_sequence');

Now the sequence is created and linked to the desired table .

If the table is empty or max id is 0 then we dont have to set value for the sequence .

If the table contains records then we have to set intial value . Neither it will start the value from 1.

Alter the sequence now :

Selecting maximum id from the table to run

SELECT MAX(id) FROM your_table;

=> SELECT MAX(id) FROM test_table;

The value should be higher than the record.

SELECT nextval('your_table_id_seq');

=> SELECT nextval('test_table_id_seq');

If it's not higher... run this set the sequence last to your highest pid it.

SELECT setval('your_table_id_seq', (SELECT MAX(id) FROM your_table));

=> SELECT setval('test_table_id_seq', (SELECT MAX(id) FROM test_table));

If your tables might have no rows
-- false means the set value will be returned by the next nextval() call

SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1));

=>SELECT setval('test_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM test_table), 1));