Programming, robotics, traveling

01 Jan 2021

Postgres: Reset primary key sequence

Hello there!

I’ve met a problem with using SQLAlchemy framework. Look below:


An error in commit() cause the call to get a new sequence of the table primary key. It updates the sequence number like:

SELECT nextval('table_id_seq');

I’d expected that rollback() must roll it back, but it didn’t. I tried to find solution at but it was unsuccessful.


First what may be found is the next command:

SELECT setval('table_id_seq', 1)

If add here a function that will be calculating maximum value of id col in the table (source: gist/henriquemenezes):

SELECT setval('table_id_seq', (SELECT MAX(id) FROM table) + 1);

It will work, but not enough. Look at second part:

(SELECT MAX(id) FROM table) + 1

If table is empty, it will just return NULL (because NULL and INTEGER cannot be summed).

A little bit searching at and at pushed the idea to replace with next construction:

(SELECT COALESCE(MAX(id), 0) FROM table) + 1

It will return 0, if the table is empty.

SELECT setval('table_id_seq', (SELECT COALESCE(MAX(id), 0) FROM table) + 1);

But when it is called it also incremented sequence. If it returns 1, that next request executed with 2. Let’s remove + 1:

SELECT setval('table_id_seq', SELECT COALESCE(MAX(id), 0) FROM table);

If the table is empty, the command can be reproduced as:

> SELECT setval('post_id_seq', 0);
ERROR:  setval: value 0 is out of bounds for sequence "post_id_seq" (1..2147483647)

I found solution at


It works differently: it doesn’t increment counter after calling. And we can back + 1.

There I can’t completely understand why that needs?

UPDATE t SET idcolumn=nextval('seq');

But it construction requires a constant and cannot be concatenated with SELECT query (source at

After burning some time in google, I’d found two sources:

I’ve cretated next procedure with that:

DO $$
	maxid integer;
	SELECT (SELECT COALESCE(MAX(id), 0) FROM table INTO maxid) + 1;
	EXECUTE 'ALTER SEQUENCE table_id_seq RESTART WITH ' || maxid;

Using on Python

It is strange, but I’ve not started up this at SQLAlchemy (source

result = db.engine.execute("<sql here>")

I made it by psycopg2. Sources:,

def reset_counter_id(table_name):

    sql = "DO $$ DECLARE maxid integer; BEGIN SELECT 1 + (SELECT COALESCE(MAX(id), 0) FROM " + table_name + " INTO maxid); EXECUTE 'ALTER SEQUENCE " + table_name + "_id_seq RESTART WITH ' || maxid; END; $$;"

    # pip install psycopg2-binary
    import psycopg2
    conn = psycopg2.connect(dbname='postgres', user='postgres', password='example', host='localhost', port="5432")
    cursor = conn.cursor()

I am using it with rollback() function.

Thank you for the reading!