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
Did you maybe install uuid-ossp into a separate schema that is not on the search_path? — a_horse_with_no_name

Haleemur Ali Haleemur Ali · Accepted Answer · 2017-04-28T17:47:30

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.

Postgres UUID type error

1 Answers