• Blog posts

    Bio

    By David Crowther

    PostGIS – Sequence - Max Value - 1.png

    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.

    PostGIS – Sequence - Max Value - 2.png

    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";

    PostGIS – Sequence - Max Value - 3.png

    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,…

    PostGIS – Sequence - Max Value - 4.png

    …. 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.

    PostGIS – Sequence - Max Value - 5.png

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

    PostGIS – Sequence - Max Value - 6.png

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

    PostGIS – Sequence - Max Value - 7.png

    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.

    PostGIS – Sequence - Max Value - 8.png

    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.

    PostGIS – Sequence - Max Value - 9.png

    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;

    PostGIS – Sequence - Max Value - 10.png

    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');

    PostGIS – Sequence - Max Value - 11.png

    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;

    PostGIS – Sequence - Max Value - 12.png

    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.

    PostGIS – Sequence - Max Value - 13.png

    Step 4 – identify the current Next value in the Sequence

    SELECT nextval('hospitals_fid_seq');

    PostGIS – Sequence - Max Value - 14.png

    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));

    PostGIS – Sequence - Max Value - 15.png

    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.

    PostGIS – Sequence - Max Value - 16.png

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

    PostGIS – Sequence - Max Value - 17.png

Comments

0 comments

Please sign in to leave a comment.