How to Reset the Primary Key Sequence in PostgreSQL?

When we use the SERIAL or IDENTITY column as the primary key in PostgreSQL, the values will be generated by the sequence. But when we delete records or perform bulk inserts, the sequence will not align properly with the MAX(id) value in the table. It may result in duplicate key violations. In this blog, you will learn in detail about resetting the primary key sequence and when we need to reset the sequence in PostgreSQL. 

Table of Contents:

• What is a Sequence in PostgreSQL?
• Why Do We Need to Reset the Primary Key Sequence in PostgreSQL?
• When Should You Reset a Sequence?
• Methods to Reset Primary Key Sequence in PostgreSQL
  • Method 1: Using setval() with Max(id) in PostgreSQL
  • Method 2: Using ALTER SEQUENCE to restart in PostgreSQL
  • Method 3: Using pg_get_serial_sequence() for Dynamic Queries in PostgreSQL
• Alternative Method Using TRUNCATE … RESTART IDENTITY in PostgreSQL
• Creating a PL/pgSQL Function to Reset Sequences for All Tables in PostgreSQL
• Performance Considerations
• Conclusion

What is a Sequence in PostgreSQL?

In PostgreSQL there is a special database object named sequence that generates unique numbers. While querying if a column is defined as SERIAL, then PostgreSQL will create an associated sequence. 

Example:

CREATE TABLE vehicles (
    id SERIAL PRIMARY KEY,
    model VARCHAR(50),
    year INT
);

Output:

Explanation: PostgreSQL will create the primary key as the sequence “vehicle_id_seq.” As the default conversion will be table_name_seq. 

Whenever we insert a value, PostgreSQL will give output as

INSERT INTO vehicles (model, year) VALUES ('Toyota Corolla', 2018);

Output:

Explanation: The sequence automatically assigns the value. The nextval function is called internally to ensure unique values for the primary key. 

Master Primary Key Sequence in PostgreSQL – Unlock Advanced SQL Skills Today!

Enroll now and transform your future!

Explore Program

Why Do We Need to Reset the Primary Key Sequence in PostgreSQL?

1. When we restore data manually or from a backup, the sequence will not update its unique ID, it will continue with the older sequence ID, which leads to duplicate key violations. 

2. If you manually insert or update a row with a specific primary key value instead of using the default, the sequence will not update the latest maximum value in the table automatically. 

3. When we delete or truncate a bulk of data in the table without using CASCADE, that will lead the sequence to have the outdated value. 

4. When you are working on fixing a corrupted file or out-of-sync sequence, it may cause an unexpected error, so resetting the sequence will make sure the data is not lost and prevent corruption. 

When Should You Reset a Sequence?

You can reset the Sequence when 

1. Manually inserted values bypassed the sequence.

INSERT INTO vehicles (id, model, year) VALUES (100, 'Ford F-150', 2021)

This will leave the sequence unaware that there is an ID value of 100 that exists.

2. When deleting the rows, it will cause gaps in IDs.

DELETE FROM vehicles WHERE id = 5;

While deleting a value or row, it won’t affect the sequence value uniqueness, but you need to reset the sequence to get the highest ID in the table. 

3. When importing the bulk data, it will reset the IDs incorrectly. If you insert the data manually with the help of the INSERT command or COPY, this will not change their unique IDs. It will be stuck at older values. 

Now, let’s create a dataset to learn how to reset the primary key sequence in PostgreSQL.

Example:

-- Create the vehicle table
CREATE TABLE vehicles (
    id SERIAL PRIMARY KEY,
    model VARCHAR(50),
    year INT
);
-- Insert sample data
INSERT INTO vehicles (model, year) VALUES 
('TATA Punch', 2018),
('Kia Seltos', 2019),
('Tesla Model Y', 2020);
-- View the table data
SELECT * FROM vehicles;

Output:

This is how the table will look after creation.

Methods to Reset Primary Key Sequence in PostgreSQL

There are some approaches that can be used to reset the sequence in PostgreSQL, like setval() with MAX(id), ALTER SEQUENCE, pg_get_serial_sequence() for dynamic queries, etc. 

Method 1: Using setval() with Max(id) in PostgreSQL

The setval() with MAX(id) in PostgreSQL will make sure that the sequence starts from the highest existing ID.

Example:

-- Find the sequence name
SELECT pg_get_serial_sequence('vehicles', 'id');
-- Reset sequence to the highest current ID
SELECT setval('vehicles_id_seq', COALESCE(MAX(id), 1), TRUE) FROM vehicles;
-- Insert a new record to confirm it works
INSERT INTO vehicles (model, year) VALUES ('Chevrolet Malibu', 2021);
-- Check the table again
SELECT * FROM vehicles;

Output:

Explanation: The setval() with MAX (id) first finds the already existing Sequence and then resets the sequence to update the unique value and then verifies the change. 

Method 2: Using ALTER SEQUENCE to restart in PostgreSQL

The ALTER SEQUENCE in PostgreSQL is used to restart the sequence to a custom value. This will force a sequence to restart from a specific number. When it is used in already existing data, it can cause a duplicate key value error. So, it is advisable to use this method in the new sequence. 

Example: if we use this in the already existing table,

-- Restart the sequence from 1 (CAUTION: May cause conflicts if data exists)
ALTER SEQUENCE vehicles_id_seq RESTART WITH 1;
-- Insert a new record (it will get id = 1, causing a conflict if records exist)
INSERT INTO vehicles (model, year) VALUES ('Nissan Altima', 2022);
-- View the updated table
SELECT * FROM vehicles;

Output:

Explanation: The error exists because there is already a unique primary key that exists. So, if we force a specific number that already exists, then it will lead to duplicate key errors. Make sure that you use the ALTER method only on an empty table 

Using new data for resetting the unique value:

-- Ensure the sequence is set correctly to avoid conflicts
SELECT setval('vehicles_id_seq', COALESCE((SELECT MAX(id) FROM vehicles), 0) + 1, false);
-- Insert a new record safely
INSERT INTO vehicles (model, year) VALUES ('Nissan Altima', 2022);
-- View the updated table
SELECT * FROM vehicles;

Output:

Explanation: Instead of forcing a value, it created a new value using the COALESCE function and named a unique sequence value. 

Method 3: Using pg_get_serial_sequence() for Dynamic Queries in PostgreSQL

The pg_get_serial_sequence() finds and resets the data dynamically without specifying the name manually. It is very useful for large datasets. 

Example:

--PL/pgSQL block to reset the sequence correctly
DO $$ 
DECLARE 
    seq_name TEXT;
BEGIN 
    -- Get the sequence name dynamically
    SELECT pg_get_serial_sequence('vehicles', 'id') INTO seq_name;
    -- Reset the sequence correctly to avoid skipping IDs
    EXECUTE format(
        'SELECT setval(''%s'', COALESCE((SELECT MAX(id) FROM vehicles), 0) + 1, false)', 
        seq_name
    );
END $$;
--Verify the current sequence value
SELECT last_value FROM public.vehicles_id_seq;
--Manually ensure the next ID is correct (if needed)
SELECT setval('public.vehicles_id_seq', (SELECT MAX(id) FROM vehicles) + 1, false);
--Insert a new test record to check the next ID
INSERT INTO vehicles (model, year) VALUES ('Nissan Altima', 2022);
--View the updated table
SELECT * FROM vehicles;

Output:

Explanation: It automatically gets the correct sequence name and manually fixes the sequence, then inserts a new record to check the correctness. 

Alternative Method Using TRUNCATE … RESTART IDENTITY in PostgreSQL

This method clears all the data in the table with the unique sequence value. First it will truncate all the values and then restart the identity by creating a new record in the table.

Example:

-- Truncate table and reset sequence
TRUNCATE TABLE vehicles RESTART IDENTITY;
-- Insert new data (will start from 1)
INSERT INTO vehicles (model, year) VALUES ('Mazda CX-5', 2024);
-- Check table data
SELECT * FROM vehicles;

Output:

Explanation: The TRUNCATE command deleted all the records from the table, and then the RESTART IDENTITY command created a new value for new records in the table. 

Creating a PL/pgSQL Function to Reset Sequences for All Tables in PostgreSQL

The PL/pgSQL function to reset sequences for all tables is used to dynamically reset the bulk data in a table. 

Example:

CREATE OR REPLACE FUNCTION reset_all_sequences()
RETURNS void AS
$$
DECLARE
    seq_record RECORD;
BEGIN
    FOR seq_record IN 
        SELECT c.oid::regclass::text AS seqname
        FROM pg_class c
        WHERE c.relkind = 'S'  -- 'S' stands for sequences
    LOOP
        EXECUTE format('SELECT setval(''%s'', COALESCE((SELECT MAX(id) FROM %s), 1), TRUE)', seq_record.seqname, split_part(seq_record.seqname, '_', 1));
    END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT reset_all_sequences();

Output:

Explanation: The bulk reset resets all the data in the table, then creates a new sequence value. 

Performance Considerations 

Start Your SQL Adventure!

Start Your SQL Journey for Free Today

Explore Program

Conclusion

In PostgreSQL, resetting the primary key sequence ensures that the records that are newly updated have to receive their unique ID without any error, especially after manually inserting, updating, deleting, or bulk importing. We can use various techniques such as setval() with MAX(id), ALTER SEQUENCE, pg_get_serial_sequence(), and TRUNCATE … RESTART IDENTITY. These approaches are very useful based on the specific needs of the user. For bulk operations and dynamic records, we can use the PL/pgSQL method, which automates sequence resetting across multiple tables. Choosing the right method will help you maintain data integrity and prevent duplicate key violations. 

To learn more about SQL, you can check out SQL Certifications and upgrade your knowledge by practicing these Interview Questions. 

How to Reset the Primary Key Sequence in PostgreSQL – FAQs