PostGIS – How do you Create a Unique ID Field?

By David Crowther

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.

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.

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

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;

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

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.

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

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