• Blog posts

    Bio

    By David Crowther

    PostGIS___How_do_you_Create_a_Unique_ID_Field_-_1..JPG

    Question:

    How can you create a Unique ID field within a PostGIS table?

    Answer:

    It is always good practice to have a column that has unique values in your PostGIS tables. This enables you to set a column with unique values as the Primary Key which is a pre-requisite for being able to edit PostGIS tables.

    Many spatial importer tools will create a Unique ID field for you when you import the source file (e.g. SHP TAB) into your PostGIS Database. For example the Database > Import Vector Layer tool in QGIS will create a unique field on import where you can define the field name e.g. ogr_fid.

    PostGIS___How_do_you_Create_a_Unique_ID_Field_-_2.JPG

    However, if you already have a file uploaded into PostGIS and the Unique ID field is missing, you can create a new Unique ID field using a simple line of SQL. Here we have a PostGIS table of Lower Super Output Areas in Shrewsbury.

    PostGIS___How_do_you_Create_a_Unique_ID_Field_-_3.jpg

    Currently the PostGIS table has no Constraints/Primary Key assigned.

    PostGIS___How_do_you_Create_a_Unique_ID_Field_-_4.JPG

    In a SQL Editor window we can create a new field of Unique Values and set that as the Primary Key using the following syntax:

    ALTER TABLE table_name ADD COLUMN FID SERIAL PRIMARY KEY;

    PostGIS___How_do_you_Create_a_Unique_ID_Field_-_5.JPG

    After running the script the results will show in the summary/statistics window.

    PostGIS___How_do_you_Create_a_Unique_ID_Field_-_6.JPG

    Once executed, if you Refresh the PostGIS Table and re-view the records you will see that a new column called FID has been added with Unique ID values populated.

    PostGIS___How_do_you_Create_a_Unique_ID_Field_-_7.JPG

    Viewing the Table Properties we can see that the Primary Key has been assigned to the new FID field.

    PostGIS___How_do_you_Create_a_Unique_ID_Field_-_8.JPG

    We have now successfully added a new Unique ID column and assigned this as the Primary Key for our PostGIS table.