I have a shp file with about 500 odd records in it. It has an ID field but I want to add in a new field with a unique reference number. Can I use the field calculator to generate a unique reference? I’m looking for the reference to be alpha numeric, so something like UG_1 or UG1. Alternatively I could load the data into PostGIS and create the field and reference here but I’m still learning the ropes of SQL statements so I’m presuming that this is possible?


Use the Field Calculator's record option '$id' and add +1 after the expression to avoid a Zero-value. To create the text, simply use a string-expression.

'UG_' || ($id + 1)

  • Just tried this and got the following 'An error occured while evaluating the calculation string: Cannot convert 'UG_0' to double' So i removed the + 1 and everything seems to be ok. Just have to change the UG_0 record.
    – geosmiles
    Dec 3 '12 at 10:52
  • 1
    My bad, I forgot to include brackets... 'UG_' || ($id + 1)
    – GR_
    Dec 3 '12 at 10:55
  • 1
    Top bananas, that worked a treat. Cheers
    – geosmiles
    Dec 3 '12 at 11:32

in PostGIS

    -- make the "id" column a primary key; this also creates
    -- a UNIQUE constraint and a b+-tree index on the column
    name  TEXT,
    the_geom geometry

then you can INSERT data like this

INSERT INTO land (name, the_geom) VALUES ('small land', ST_PointFromText('POINT(-71.064544 42.28787)'));


INSERT INTO land (name, the_geom, id) VALUES ('small land', ST_PointFromText('POINT(-71.064544 42.28787)'), DEFAULT);

Btw. My opinion is that using ALPHA NUMERIC serials is bad idea. It's just easier to compare int's when you need to

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.