How to generate uuid with PostgreSQL 8.4.4 on Ubuntu 10.04?

Question

Hi guys,

I am running PostgreSQL 8.4.4 with Ubuntu 10.04.

I am trying to generate uuid but can't find a way to do it.

I do have the uuid-ossp.sql in /usr/share/postgresql/8.4/contrib/uuid-ossp.sql

When I try this is what I get :

postgres=# SELECT uuid_generate_v1();
ERROR:  function uuid_generate_v1() does not exist
LINE 1: SELECT uuid_generate_v1();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Any idea ?

Answer 1

The stuff in contrib aren't run automatically. You have to run it yourself to install the functions. I don't know about the 8.4 version, but in the 8.3 version it appears to only install it per-database, so open up the database you're using in psql and issue the command \i /usr/share/postgresql/8.4/contrib/uuid-ossp.sql

Answer 2

I saw this in my PostgreSQL travels. It requires the pgcrypto contrib module.

CREATE OR REPLACE FUNCTION generate_uuid() RETURNS UUID AS
$$
SELECT ENCODE(GEN_RANDOM_BYTES(16), 'hex')::UUID
$$ LANGUAGE SQL IMMUTABLE;