Sometimes you need to add or modify a sequence in PostgreSQL. Maybe you migrated your data from another engine, such as MySQL, and lost your primary-key sequence (Auto Increment in MySQL). Or maybe you simply need to modify the next value your sequence provides.

Let's assume you just migrated your portfolio website from a MySQL database, and you have a table called project. The project table has a primary-key called id that you want to 'auto increment' each time a new project is added to the database. The following demonstrates how to create a sequence and use it to provide a new default value for project.id each time a new one is created:

-- Create a Sequence
CREATE SEQUENCE project_id_seq;

-- Use it to provide a new value for each project ID
ALTER TABLE project 
ALTER id 
SET DEFAULT NEXTVAL('project_id_seq');

Now, each time a row is added to the project table, a new ID will be generated. Great, right? Well there's one more problem. If your project table already has rows in it, each row will already have an ID. Our new project_id_seq sequence is going to start at 1, which means there's a good chance the IDs are going to overlap at some point, and your unique constraint on the primary key will fail.

Luckily, modifying the sequence to start at a value other than 1 is very straightforward:

-- Change the starting value of the sequence
ALTER SEQUENCE project_id_seq RESTART 3000;

So now our sequence will restart with a value of 3000, and increment from there. However, the following demonstrates a more dynamic approach, where we can set the new value to be the highest ID in the table, plus one:

-- Similar to above, but more dynamic
SELECT SETVAL('project_id_seq', (SELECT MAX(id) + 1 FROM project));

Here, we call the SETVAL function which is used under the hood in the ALTER SEQUENCE command above, and set the value to the max ID in the project table, plus one. So, for example, if the max ID in project is currently 275, the sequence would now start at 276.