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);

Answer 1

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.

Answer 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" ;