0
286
ViewsPostgres UUID type error
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);
2 answers
Answer question0
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.
0
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" ;