Wednesday, 18 May 2016

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));