Postgres UUID type error

Question

I'm trying to create a UUID id in a table with PostgreSQL. I tried with:

id uuid PRIMARY KEY DEFAULT uuid_generate_v4()

But I get:

ERROR: function uuid_generate_v4() does not exist HINT: No function matches the given name and argument types. You might need to add explicit type casts.

I tried with adding the schema like: id uuid PRIMARY KEY DEFAULT public.uuid_generate_v4() (as seen in a comment here)

I also checked if the extension is there (SELECT * FROM pg_available_extensions;), and yes I have it installed in the PostgreSQL database:

I read that if the Postgres is runing in --single mode, this may not work, but I don't know how to test it or if there is any way to do it.

Somebody knows how I can resolve the problem? Or any other option? Is it a good idea to use like this:

SET DEFAULT uuid_in(md5(random()::text || now()::text)::cstring);

"Note: If you only need randomly-generated (version 4) UUIDs, consider using the gen_random_uuid() function from the pgcrypto module instead." — Philip Tzou
– Philip Tzou, Commented Apr 28, 2017 at 17:38
Did you maybe install uuid-ossp into a separate schema that is not on the search_path? — user330315
– user330315, Commented Apr 28, 2017 at 17:56

Rafael Aguilar · Accepted Answer · 2019-05-19 21:52:47Z

37

Because the function uuid_generate_v4 is not found, it suggests that the extension uuid-ossp is not loaded

pg_available_extensions lists the extensions available, but not necessarily loaded.

to see the list of loaded extensions query the view pg_extension as such:

select * from pg_extension;

To load the uuid-ossp extension run the following:

CREATE EXTENSION "uuid-ossp";

note: this will require super user privileges.

After the uuid-ossp extension is successfully loaded, you should see it in the pg_extension view & the function uuid_generate_v4 should be available.

edited May 19, 2019 at 21:52

Rafael Aguilar

3,2791 gold badge28 silver badges31 bronze badges

answered Apr 28, 2017 at 17:47

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JP. Aulet Over a year ago

If I use: select * from pg_extensions; I get the error: ERROR: relation "pg_extensions" does not exist. For the moment I'm using: uuid_in(md5(random()::text || now()::text)::cstring)

Haleemur Ali Over a year ago

I'm sorry, that was a typo on my part. The view is actually called pg_extension, updating post

bastula Over a year ago

Also make sure you are in the right database before doing this. Extensions seem to be loaded on a per-database level.

JtD · Accepted Answer · 2021-08-03 22:41:24Z

2

In my case I needed to add the schema to the function call like this: app.uuid_generate_v4()

instead of this: uuid_generate_v4()

I found the schema for each extension by running this query:

SELECT 
pge.extname,
pge.extversion,
pn.nspname AS schema
FROM pg_extension pge 
JOIN pg_catalog.pg_namespace pn ON pge.extnamespace = pn."oid" ;

answered Aug 3, 2021 at 22:41

JtD

417 bronze badges

Comments

rai sahab · Accepted Answer · 2023-09-25 14:18:26Z

2

I was facing the same problem. I just deleted extension and run:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

And my problem solved.

answered Sep 25, 2023 at 14:18

rai sahab

211 bronze badge

Comments

Brian Tompsett - 汤莱恩 · Accepted Answer · 2023-05-29 21:28:39Z

1

Sometime you've just to run the line:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

just before the creation of the table

edited May 29, 2023 at 21:28

Brian Tompsett - 汤莱恩

5,92772 gold badges63 silver badges135 bronze badges

answered May 29, 2023 at 8:21

Glen

111 bronze badge

Comments

Basil Bourque · Accepted Answer · 2025-10-10 08:55:42Z

tl;dr

Just use uuidv7 or uuidv4 built-in functions rather than calling the uuid-ossp extension.

Use Version 7 UUIDs for efficient indexing.

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

Now built-in

Postgres 18+ comes with a Version 7 (time + random) UUID generating function, uuidv7.

Postgres 18+ and many earlier versions come with a Version 4 (entirely random) UUID generating function, gen_random_uuid (also known as uuidv4 in Postgres 18+).

So you may no longer need the uuid-ossp extension.

Version 7 UUID

In 2024, RFC 9562 invented two new Versions of UUID. Version 6 is a reworking of Version 1, both based on points in time and space. Version 7 is brand-new, based on a point in time plus random bits.

Index-friendly

Both Versions 6 & 7 have a bit-layout designed to be friendly to indexing engines. These Versions solve the indexing inefficiency problem encountered with larger tables using Versions 1-5 UUIDs.

Prefer Version 7 over Version 1 & 6 if possible, such as greenfield projects.

Here is the bit-layout of Version 7 UUIDs.

 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                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

The implementation of the Postgres function uuidv7 takes the specification-granted option of using a sub-millisecond timestamp fraction in place of rand_a seen in diagram above.

Collectives™ on Stack Overflow

Postgres UUID type error

5 Answers 5

3 Comments

Comments

Comments

Comments

tl;dr

Now built-in

Version 7 UUID

Index-friendly

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

tl;dr

Now built-in

Version 7 UUID

Index-friendly

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related