Generating a UUID in Postgres for Insert statement?

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'.

Without extensions (cheat)

If you need a valid v4 UUID

SELECT uuid_in(overlay(overlay(md5(random()::text || ':' || random()::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

---

Or you can simply get UUID-like value by doing this (if you don't care about the validity):

SELECT uuid_in(md5(random()::text || random()::text)::cstring);

output>> c2d29867-3d0b-d497-9191-18a9d8ee7830

(works at least in 8.4)

PostgreSQL 13 supports natively gen_random_uuid ():

PostgreSQL includes one function to generate a UUID:

gen_random_uuid () → uuid

This function returns a version 4 (random) UUID. This is the most commonly used type of UUID and is appropriate for most applications.

db<>fiddle demo