Postgres: Reset primary key sequence

Hello there!

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

db.session.add(AnObject)
try:
    db.session.commit()
except:
    db.session.rollback()

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 michaelcho.me but it was unsuccessful.

Solving

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 www-cs-students.stanford.edu and at info-comp.ru 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 stackoverflow.com:

ALTER SEQUENCE seq RESTART WITH 1;

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 stackoverflow.com).

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

• dynamic sql query in postgres
• How to declare a variable in a PostgreSQL query

I’ve cretated next procedure with that:

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

Using on Python

It is strange, but I’ve not started up this at SQLAlchemy (source stackoverflow.com):

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

I made it by psycopg2. Sources: khashtamov.com, dev-gang.ru.

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()
    cursor.execute(sql)
    cursor.close()
    conn.commit()
    conn.close()

I am using it with rollback() function.

Thank you for the reading!