How can I generate a unique string per record in a table in Postgres?

Question

Say I have a table like posts, which has typical columns like id, body, created_at. I'd like to generate a unique string with the creation of each post, for use in something like a url shortener. So maybe a 10-character alphanumeric string. It needs to be unique within the table, just like a primary key.

Ideally there would be a way for Postgres to handle both of these concerns:

1. generate the string
2. ensure its uniqueness

And they must go hand-in-hand, because my goal is to not have to worry about any uniqueness-enforcing code in my application.

Answer 1

I don't claim the following is efficient, but it is how we have done this sort of thing in the past.

CREATE FUNCTION make_uid() RETURNS text AS $$ DECLARE     new_uid text;     done bool; BEGIN     done := false;     WHILE NOT done LOOP         new_uid := md5(''||now()::text||random()::text);         done := NOT exists(SELECT 1 FROM my_table WHERE uid=new_uid);     END LOOP;     RETURN new_uid; END; $$ LANGUAGE PLPGSQL VOLATILE; 

make_uid() can be used as the default for a column in my_table. Something like:

ALTER TABLE my_table ADD COLUMN uid text NOT NULL DEFAULT make_uid(); 

md5(''||now()::text||random()::text) can be adjusted to taste. You could consider encode(...,'base64') except some of the characters used in base-64 are not URL friendly.

Answer 2

All existing answers are WRONG because they are based on SELECT while generating unique index per table record. Let us assume that we need unique code per record while inserting: Imagine two concurrent INSERTs are happening same time by miracle (which happens very often than you think) for both inserts same code was generated because at the moment of SELECT that code did not exist in table. One instance will INSERT and other will fail.

First let us create table with code field and add unique index

CREATE TABLE my_table (     code TEXT NOT NULL );  CREATE UNIQUE INDEX ON my_table (lower(code)); 

Then we should have function or procedure (you can use code inside for trigger also) where we 1. generate new code, 2. try to insert new record with new code and 3. if insert fails try again from step 1

CREATE OR REPLACE PROCEDURE my_table_insert() AS $$ DECLARE     new_code TEXT; BEGIN      LOOP         new_code := LOWER(SUBSTRING(MD5(''||NOW()::TEXT||RANDOM()::TEXT) FOR 8));         BEGIN             INSERT INTO my_table (code) VALUES (new_code);             EXIT;         EXCEPTION WHEN unique_violation THEN          END;     END LOOP;  END; $$ LANGUAGE PLPGSQL; 

This is guaranteed error free solution not like other solutions on this thread