Generating a UUID in Postgres for Insert statement?

Question

My question is rather simple. I'm aware of the concept of a UUID and I want to generate one to refer to each 'item' from a 'store' in my DB with. Seems reasonable right?

The problem is the following line returns an error:

honeydb=# insert into items values( uuid_generate_v4(), 54.321, 31, 'desc 1', 31.94); ERROR:  function uuid_generate_v4() does not exist LINE 2: uuid_generate_v4(), 54.321, 31, 'desc 1', 31.94);         ^ HINT:  No function matches the given name and argument types. You might need to add explicit type casts. 

I've read the page at: http://www.postgresql.org/docs/current/static/uuid-ossp.html

I'm running Postgres 8.4 on Ubuntu 10.04 x64.

Answer 1

uuid-ossp is a contrib module, so it isn't loaded into the server by default. You must load it into your database to use it.

For modern PostgreSQL versions (9.1 and newer) that's easy:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; 

but for 9.0 and below you must instead run the SQL script to load the extension. See the documentation for contrib modules in 8.4.

For Pg 9.1 and newer instead read the current contrib docs and CREATE EXTENSION. These features do not exist in 9.0 or older versions, like your 8.4.

If you're using a packaged version of PostgreSQL you might need to install a separate package containing the contrib modules and extensions. Search your package manager database for 'postgres' and 'contrib'.

Answer 2

Without extensions (cheat)

SELECT uuid_in(md5(random()::text || clock_timestamp()::text)::cstring);  output>> c2d29867-3d0b-d497-9191-18a9d8ee7830 

(works at least in 8.4)

• Thanks to @Erwin Brandstetter for clock_timestamp() explanation.

---

If you need a valid v4 UUID

SELECT uuid_in(overlay(overlay(md5(random()::text || ':' || clock_timestamp()::text) placing '4' from 13) placing to_hex(floor(random()*(11-8+1) + 8)::int)::text from 17)::cstring); 

* Thanks to @Denis Stafichuk @Karsten and @autronix

---

Also, in modern Postgres, you can simply cast:

SELECT md5(random()::text || clock_timestamp()::text)::uuid