13

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:

enter image description here

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);
Community
  • 1
  • 1
JP. Aulet
  • 4,375
  • 4
  • 26
  • 39
  • 2
    "Note: If you only need randomly-generated (version 4) UUIDs, consider using the `gen_random_uuid()` function from the [`pgcrypto`](https://www.postgresql.org/docs/9.5/static/pgcrypto.html) module instead." – Philip Tzou Apr 28 '17 at 17:38
  • 1
    i don't think you have `uuid-ossp` loaded – Haleemur Ali Apr 28 '17 at 17:42
  • 1
    Did you maybe install `uuid-ossp` into a separate schema that is not on the search_path? –  Apr 28 '17 at 17:56

3 Answers3

31

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.

Rafael Aguilar
  • 3,084
  • 1
  • 25
  • 31
Haleemur Ali
  • 26,718
  • 5
  • 61
  • 85
  • 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)` – JP. Aulet Apr 30 '17 at 16:45
  • I'm sorry, that was a typo on my part. The view is actually called [`pg_extension`](https://www.postgresql.org/docs/current/static/catalog-pg-extension.html), updating post – Haleemur Ali Apr 30 '17 at 17:55
  • 1
    Also make sure you are in the right database before doing this. Extensions seem to be loaded on a per-database level. – bastula Jan 09 '19 at 05:11
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" ;
JtD
  • 41
  • 7
0

Sometime you've just to run the line:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

just before the creation of the table

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Glen
  • 1