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

PostgresSQL 18:

• uuidv4 → uuid Generate a version 4 (random) UUID.
• uuidv7 ([shift interval]) → uuid Generate a version 7 (time-ordered) UUID

SELECT uuidv4(), uuidv7();

db<>fiddle demo 2

Update: In Postgres 18+, you may no longer need the uuid-ossp extension discussed in this Answer. Instead, see my new Answer about Version 7 UUIDs.

The answer by Craig Ringer is correct. Here's a little more info for Postgres 9.1 and later.

Is Extension Available?

You can only install an extension if it has already been built for your Postgres installation (your cluster in Postgres lingo). For example, I found the uuid-ossp extension included as part of the installer for Mac OS X kindly provided by EnterpriseDB.com. Any of a few dozen extensions may be available.

To see if the uuid-ossp extension is available in your Postgres cluster, run this SQL to query the pg_available_extensions system catalog:

SELECT * FROM pg_available_extensions;

Install Extension

To install that UUID-related extension, use the CREATE EXTENSION command as seen in this this SQL:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Beware: I found the QUOTATION MARK characters around extension name to be required, despite documentation to the contrary.

The SQL standards committee or Postgres team chose an odd name for that command. To my mind, they should have chosen something like "INSTALL EXTENSION" or "USE EXTENSION".

Verify Installation

You can verify the extension was successfully installed in the desired database by running this SQL to query the pg_extension system catalog:

SELECT * FROM pg_extension;

UUID as default value

For more info, see the Question: Default value for UUID column in Postgres

The Old Way

The information above uses the new Extensions feature added to Postgres 9.1. In previous versions, we had to find and run a script in a .sql file. The Extensions feature was added to make installation easier, trading a bit more work for the creator of an extension for less work on the part of the user/consumer of the extension. See my blog post for more discussion.

Types of UUIDs

By the way, the code in the Question calls the function uuid_generate_v4(). This generates a type known as Version 4 where nearly all of the 128 bits are randomly generated. While this is fine for limited use on smaller set of rows, if you want to virtually eliminate any possibility of collision, use another "version" of UUID.

For example, the original Version 1 combines the MAC address of the host computer with the current date-time and an arbitrary number, the chance of collisions is practically nil.

For more discussion, see my Answer on related Question.

UUID Version 7

New in Postgres 18 is support for the new kind of UUID, Version 7 defined in RFC 9562. Much more efficient for use as keys in a database. See UUIDv7 Comes to PostgreSQL 18 by Gwen Shapira and Waiting for PostgreSQL 18 – Add UUID version 7 generation function. by depesz.

pgcrypto Extension

As of Postgres 9.4, the pgcrypto module includes the gen_random_uuid() function. This function generates one of the random-number based Version 4 type of UUID.

Get contrib modules, if not already available.

sudo apt-get install postgresql-contrib-9.4

Use pgcrypto module.

CREATE EXTENSION "pgcrypto";

The gen_random_uuid() function should now available;

Example usage.

INSERT INTO items VALUES( gen_random_uuid(), 54.321, 31, 'desc 1', 31.94 ) ;

Quote from Postgres doc on uuid-ossp module.

Note: If you only need randomly-generated (version 4) UUIDs, consider using the gen_random_uuid() function from the pgcrypto module instead.

Update from 2021, There is no need for a fancy trick to auto generate uuid on insert statement.

Just do one thing:

1. Set default value of DEFAULT gen_random_uuid () to your uuid column. That is all.

Say, you have a table like this:

CREATE TABLE table_name (
    unique_id UUID DEFAULT gen_random_uuid (),
    first_name VARCHAR NOT NULL,
    last_name VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    phone VARCHAR,
    PRIMARY KEY (unique_id)
);

Now you need NOT to do anything to auto insert uuid values to unique_id column. Because you already defined a default value for it. You can simply focus on inserting onto other columns, and postgresql takes care of your unique_id. Here is a sample insert statement:

INSERT INTO table_name (first_name, last_name, email, phone) 
VALUES (
    'Beki',
    'Otaev',
    '[email protected]',
    '123-456-123'
)

Notice there is no inserting into unique_id as it is already taken care of.

About other extensions like uuid-ossp, you can bring them on if you are not satisfied with postgres's standard gen_random_uuid () function. Most of the time, you should be fine without them on

Postgres v13+

INSERT INTO your_table
VALUES (gen_random_uuid(), 'value column 2', 'value column 3')

On postgres version less than 13:

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- gives:

SELECT gen_random_uuid();

On postgres version 13+:

SELECT gen_random_uuid();

In context when you don't know the postgres version but presume it's at least 9, and it matters to you not to enable the extension unnecessarily:

DO
$do$
BEGIN
  IF current_setting('server_version_num')::integer < 130000 THEN
    CREATE EXTENSION IF NOT EXISTS "pgcrypto";
  END IF;
END
$do$

SELECT gen_random_uuid();

The uuid-ossp module provides functions to generate universally unique identifiers (UUIDs)

uuid_generate_v1() This function generates a version 1 UUID.

1. Add Extension

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

2. Verify Extension

SELECT * FROM pg_extension;

3. Run Query

INSERT INTO table_name(id, column1, column2 , column3, ...) VALUES (uuid_generate_v1(), value1, value2, value3...);

Verify table data

ALTER TABLE table_name ALTER COLUMN id SET DEFAULT uuid_in((md5((random())::text))::cstring);

After reading @ZuzEL's answer, i used the above code as the default value of the column id and it's working fine.

first, you need to add extension

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

tl;dr

In Postgres 18+, use Version 7 UUID values generated by the built-in function, uuidv7.

CREATE TABLE example_ (
    id_ UUID PRIMARY KEY DEFAULT uuidv7()
);

UUID Version 7

In 2024, the UUID specification was rewritten. RFC 9562 obsoletes RFC 4122.

One of the principal goals of this new spec is inventing two new Versions of UUID. Both are designed to facilitate efficient indexing. This resolves the major problem with using UUIDs as keys in a database, where indexing becomes problematic as a table grows to millions of rows.

• Version 6 is a re-working of Version 1, both based on a points in time and space. The arbitrary bit-layout of the timestamp at the front of the UUID value is revamped to be sensible, to make indexing engines happy.
• Version 7 is a new kind of UUID, based on time plus random. Again, the bit-layout of the timestamp at the front is revamped to be sensible and index-friendly. The rest of the bits are randomly-generated, save for the required 2 bits for Variant and 4 bits for Version.

Prefer Version 7 over Version 1 & 6, if possible.

Postgres 18+

In 2025, Postgres 18 gained support for Version 7 UUIDs with the addition of a new built-in function, uuidv7. This function generates Version 7 UUID values.

The implementation of that Postgres function invokes the specification-granted option of using a finer sub-millisecond timestamp in the otherwise random bits labeled rand_a in the following bit-layout diagram.

 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                           unix_ts_ms                          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|          unix_ts_ms           |  ver  |       rand_a          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|var|                        rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                            rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Also, in Postgres 18+, the old function gen_random_uuid gets an alternative name, uuidv4. (But you should generally use Version 7, for indexing.)

For discussion, see UUIDv7 Comes to PostgreSQL 18 by Gwen Shapira at TheNile.dev. And read RFC 9562 itself; the spec is surprisingly well-written, clear and insightful, with explanations and guidance.

Note that with the new uuidv7 function being built-in, you may no longer need to install the uuid-ossp extension.

SELECT uuid_generate_v5(uuid_ns_url (), 'test');