Automatically realign identities values in PostgreSQL

Sep 28, 2023 · 3 min read

The problem of “identity misalignment” is something that commonly arises in PostgreSQL (and other similar database systems) when there is a mismatch, between the values generated by a sequence or identity column and the actual data stored in the table. This misalignment can occur for reasons, such as errors while inserting values into an identity column, mistakes during data manipulation or issues with sequence generators. It is crucial to detect and address this problem to maintain data integrity and prevent any disruptions, to user operations.

Let’s say we have an identity in a table created with the following SQL DDL.

create table mytable(
		id bigint not null generated by default as identity primary key,
		value1 varchar,
		value2 varchar
	);

Then we load some data from some other source using the id that normally should be generated by the identity (it happend when you import data from one database to another)

insert into mytable(id, value1, value2) values 
		(1,'Daniele','Teti'),
		(2,'Peter','Parker'),
		(3,'Bruce','Banner');

Good! Data are there, but when you try to insert new data using the usual way (maybe directly from the application)…

insert into mytable(value1, value2)	 values ('Jake', 'The Cat');

We get an error.

ERROR: duplicate key value violates unique constraint "mytable_pkey"
  Detail: Key ("id")=(1) already exists.

What’s happened? Simply, when we loaded data into table we told to PostgreSQL to use the value directly provided in the insert statement for the field id and not the value provided by the identity, so the identity still has value 1 becouse still no one asked anithing to her.

Wouldn’t it be nice to ask PostgreSQL to generate a script to solve the problem?

Yes, it would be… let’s do it!

Execute this query

select 
	'SELECT setval(pg_get_serial_sequence(''' 
        || pg_class.relname || ''',''' || attname || '''), (select max(' 
        || attname || ') from ' || pg_class.relname || '));' sql_stm
from 
	pg_attribute join pg_class on pg_attribute.attrelid = pg_class.oid 
where 
	attnum > 0 and attidentity = 'd'

The returned field named sql_stm contains a SQL statement for each table in the database containing an identity. Such SQL statement fixes the problem, one table at a time.

SELECT setval(pg_get_serial_sequence('approval_groups','id'), (select max(id) from approval_groups));
SELECT setval(pg_get_serial_sequence('approval_profiles','id'), (select max(id) from approval_profiles));
SELECT setval(pg_get_serial_sequence('attendance_sheets','id'), (select max(id) from attendance_sheets));
SELECT setval(pg_get_serial_sequence('clocking_points','id'), (select max(id) from clocking_points));
SELECT setval(pg_get_serial_sequence('clocking_points_profiles','id'), (select max(id) from clocking_points_profiles));
SELECT setval(pg_get_serial_sequence('clocking_points_types','id'), (select max(id) from clocking_points_types));
SELECT setval(pg_get_serial_sequence('clockings','id'), (select max(id) from clockings));
SELECT setval(pg_get_serial_sequence('commissions','id'), (select max(id) from commissions));
SELECT setval(pg_get_serial_sequence('countries','id'), (select max(id) from countries));
SELECT setval(pg_get_serial_sequence('country_festivities','id'), (select max(id) from country_festivities));
SELECT setval(pg_get_serial_sequence('department_festivities','id'), (select max(id) from department_festivities));
SELECT setval(pg_get_serial_sequence('departments','id'), (select max(id) from departments));
SELECT setval(pg_get_serial_sequence('emails','id'), (select max(id) from emails));
SELECT setval(pg_get_serial_sequence('events','id'), (select max(id) from events));
...

Just execute this in psql o other tool and your identities return to work correctly.

Do you want to know more about PostgreSQL? Check my trainings for developers and administrators (availables in english, italian and spanish).

Automatically realign identities values in PostgreSQL

Comments