How to recreate unique reference number with QGIS or PostGIS?

Question

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?

Answer 1

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)

Answer 2

in PostGIS

CREATE TABLE land (
    -- make the "id" column a primary key; this also creates
    -- a UNIQUE constraint and a b+-tree index on the column
    gid    SERIAL PRIMARY KEY,
    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)'));

Or

 
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