PostGIS – Sequence - Max Value

By David Crowther

Question:

PostGIS won’t let me insert a new record into my database table and provides this error message:

duplicate key value violates unique constraint "hospitals_pkey"

How can I resolve this?

Answer:

This issue is caused because when your application tries to insert the new record, PostGIS doesn’t know how to auto increment a value into the primary key field.

The table is therefore missing a SEQUENCE.

For example, my Dentists table in PostGIS has a SEQUENCE and a FUNCTION that allows the table to auto increment the primary key field using the next value after the Max Sequence value.

Using the following SQL select statement we can identify the Max value in the SEQUENCE for the ID field.

SELECT MAX(id) FROM public."Dentists";

The max value in the current Sequence is 4945.

And because our table has a FUNCTION that will use the next value in the Sequence to insert a new record,…

…. when using an application such as QGIS, the system already knows to use the next value in the Sequence to insert a record an update the ID (PK) field with the next incremental value.

The new record is then inserted into the table using Max Val + 1 (next value) = 4595 + 1 = 4596.

However, if we look at another table – Hospitals – we can see there is no Sequence or Function related to this table.

So, when using QGIS and trying to insert a new record, QGIS has no way of knowing how to insert the next unique ID value.

This would mean the users would need to try and guess the next sequential number or start adding values by guess work, with users likely guessing ID values that already exist.

So, it is important to ensure that your tables have a Sequence and Functions that ensure the next value is used when inserting a new record into the table.

Step 1 – Create Sequence

CREATE SEQUENCE hospitals_fid_seq OWNED BY public.hospitals.fid;

This creates a Sequence into the fid field of your table.

Step 2 – Alter table and set default value for fid to be next sequential value

ALTER TABLE hospitals ALTER COLUMN fid SET DEFAULT nextval('hospitals_fid_seq');

This alters the fid field to ensure the default value for that field is the next value using the Sequence.

Step 3 – identify the current Max value in the fid field

SELECT MAX(fid) FROM hospitals;

The current Max value in the fid field for the hospitals table is 852 and we see this when we look at the records in the table.

Step 4 – identify the current Next value in the Sequence

SELECT nextval('hospitals_fid_seq');

This shows that the new Sequence we created is currently set to start the next insert value as 1. We will need to update the Sequence to be the Max value (852).

Step 5 – update the Sequence to be the current max value

SELECT setval('hospitals_fid_seq', (SELECT MAX(fid) FROM hospitals));

The Sequence is now set to be the current max value (852) and now knows that the next value to be inserted is 853.

If we now test the table by creating a new feature using QGIS, firstly we can see that the attribute form now knows to use the next value using the hospitals fid Sequence.

Once saved the new record has been inserted into the table and the FID auto updated with the next incremental value in the sequence.