Extension exists but uuid_generate_v4 fails

Question

At amazon ec2 RDS Postgresql:

=> SHOW rds.extensions;

rds.extensions                                                                                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 btree_gin,btree_gist,chkpass,citext,cube,dblink,dict_int,dict_xsyn,earthdistance,fuzzystrmatch,hstore,intagg,intarray,isn,ltree,pgcrypto,pgrowlocks,pg_trgm,plperl,plpgsql,pltcl,postgis,postgis_tiger_geocoder,postgis_topology,sslinfo,tablefunc,tsearch2,unaccent,uuid-ossp
(1 row)

As you can see, uuid-ossp extension does exist. However, when I'm calling the function for generation uuid_v4, it fails:

CREATE TABLE my_table (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    name character varying(32) NOT NULL,

);

What's wrong with this?

Woodprint

In future, please always show the exact text of any error message. – Unshapen

Yes extension exists but database is probably in inconsistent state. One reason that may happen is if you drop the schema but leave the extension. To avoid this it's good tactic to run DROP EXTENSION IF EXISTS "uuid-ossp" CASCADE; and then CREATE EXTENSION "uuid-ossp"; (see detailed explanation in the answer of @atomCode below) – Ulaulah

If you have Postgres 13 just use gen_random_uuid() which is built in – Interoffice

Answer 1

The extension is available but not installed in this database.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Unshapen

Just to be clear, to select the db one can write \c <db name> in the pgsql console – Subsist

@CraigRinger Where I can find this doc? – Quinquepartite

When running \c <dbname> in pgsql console, I got an authentication error. I managed to work around it using the Ubuntu command line. sudo -u postgres psql <db_name> <br> From there I just run CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; as instructed. – Extrapolate

I got error 'ERROR: could not open extension control file "/usr/share/pgsql/extension/uuid-ossp.control": No such file or directory' – Cortisone

Answer 2

If the extension is already there but you don't see the uuid_generate_v4() function when you do a describe functions \df command then all you need to do is drop the extension and re-add it so that the functions are also added. Here is the issue replication:

db=# \df
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)
CREATE EXTENSION "uuid-ossp";
ERROR:  extension "uuid-ossp" already exists
DROP EXTENSION "uuid-ossp";
CREATE EXTENSION "uuid-ossp";
db=# \df
                                  List of functions
 Schema |        Name        | Result data type |    Argument data types    |  Type
--------+--------------------+------------------+---------------------------+--------
 public | uuid_generate_v1   | uuid             |                           | normal
 public | uuid_generate_v1mc | uuid             |                           | normal
 public | uuid_generate_v3   | uuid             | namespace uuid, name text | normal
 public | uuid_generate_v4   | uuid             |                           | normal

db=# select uuid_generate_v4();
           uuid_generate_v4
--------------------------------------
 b19d597c-8f54-41ba-ba73-02299c1adf92
(1 row)

What probably happened is that the extension was originally added to the cluster at some point in the past and then you probably created a new database within that cluster afterward. If that was the case then the new database will only be "aware" of the extension but it will not have the uuid functions added which happens when you add the extension. Therefore you must re-add it.

Hipparch

Yes I had that problem too and what you describe here fixed it – Ulaulah

Answer 3

Looks like the extension is not installed in the particular database you require it.

You should connect to this particular database with

 \CONNECT my_database

Then install the extension in this database

 CREATE EXTENSION "uuid-ossp";

Pachyderm

it should be psql <your_dbName> then paste this CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; – Glazunov

Answer 4

Step #1: re-install uuid-ossp extention into the exact schema:

If this is a fresh installation you can skip SET and DROP. Credits to @atomCode (details)

SET search_path TO public;
DROP EXTENSION IF EXISTS "uuid-ossp";

CREATE EXTENSION "uuid-ossp" SCHEMA public;

After this, you should see uuid_generate_v4() function IN THE RIGHT SCHEMA (when execute \df query in psql command-line prompt).

Step #2: use fully-qualified names (with schemaname. qualifier):

For example:

CREATE TABLE public.my_table (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,

Campinas

thank you. i created first by droping and created extension with schema but it didn't work, Next i went to default and write schema.uuid_generate_v4() which did the trick. – Setose

Answer 5

If you've changed the search_path, specify the public schema in the function call:

public.uuid_generate_v4()

Munmro

Answer 6

This worked for me.

create extension IF NOT EXISTS "uuid-ossp" schema pg_catalog version "1.1"; 

make sure the extension should by on pg_catalog and not in your schema...

Bellyband

Answer 7

Just add this code to the Beginning of your script

DROP EXTENSION IF EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Upthrow

Answer 8

Maybe It was the same I was facing. The uuid_generate_v4 was from the public schema and I was trying to run it in a specific schema, so to fix it I did:

SET search_path TO specific_schema;

INSERTO INTO my_table VALUES public.uuid_generate_v4();

You can check the schema where your function is running:

\df uuid_generate_v4

Or

SELECT n.nspname, p.probin, p.proname
FROM
    pg_proc p
    LEFT JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.proname like 'uuid_generate_v4';

You can check info related to the extension of the uuid-ossp like this:

SELECT * FROM pg_extension WHERE extname LIKE 'uuid-ossp';

You can add this extension case you don't have it already:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Pameliapamelina

Answer 9

if you do it from unix command (apart from PGAdmin) dont forget to pass the DB as a parameter. otherwise this extension will not be enabled when executing requests on this DB

psql -d -c "create EXTENSION pgcrypto;"

Florella

Answer 10

in my case were 3 steps. Create the database, connect to the database and create the extension. The important step is the second one, "connect to the database", and you can notice the line without ";" cause is a command and not a SQL sentence.

CREATE DATABASE database_name_here;
\connect database_name_here 
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Bostwick

Answer 11

I'm leaving this here incase it might help. I was working on a nestjs app which uses typeorm. When I tried running migrations I got the error below:

error: function uuid_generate_v4() does not exist

After reading through all responses and trying different things, I ran this block of code to install the extention if it does not exist:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Then I ran SELECT uuid_generate_v4(); which returned a positive response. But I tried running the migrations again and it still failed. Then I ran this code below:

CREATE OR REPLACE FUNCTION uuid_generate_v4() RETURNS uuid AS $$ SELECT uuid_generate_v4(); $$ LANGUAGE sql VOLATILE;

The above step creates a uuid_generate_v4() function that simply calls the real postgres function under the hood.

Then I ran this also

GRANT EXECUTE ON FUNCTION uuid_generate_v4() TO username;

Replace username with your postgres username

Then when I tried the migrations again, it worked.

Mindi

Weird. I have the same issue locally. – Bluestocking

Answer 12

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

If the above steps don't resolve the issue, consider installing the uuid-ossp extension using the package manager on your system. On many Linux distributions, it can be installed with a command like:

sudo apt-get install postgresql-contrib   # For Debian/Ubuntu

sudo yum install postgresql-contrib   # For Red Hat/CentOS

Trelliswork

Answer 13

In case anyone is still encountering this issue, here are two solutions:

The first solution involves enabling the uuid-ossp extension in your PostgreSQL database. Use the following SQL command:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

For the second solution, use the @BeforeInsert() decorator from TypeORM and the uuid npm package. Change your ID from @PrimaryGeneratedColumn('uuid') to simply @PrimaryColumn(). Then, append to the end of your entity class:

@BeforeInsert()
generateUuid() {
  if (!this.id) {
    this.id = uuidv4();
  }
}

This ensures that a UUID is generated from your application using the uuid package.

Tynishatynwald

Answer 14

None of the answers provides exact steps (for the people who are not experienced much) to resolve issue.

Here are the steps:

1. In terminal(I'm on Linux/Debian), access your PostgreSQL database through running: sudo -u postgres psql <db_name>

2. Then run following command. CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

All credit goes to @Joachim Rives. He provided answer as comment under @Craig Ringer's post.

Alviani

Answer 15

If you just need to generate a random v4 UUID and have PostgreSQL 13 or higher, you can simply use

gen_random_uuid()

It is not necessary to install an extension for this.

(more details here: https://pgpedia.info/g/gen_random_uuid-function.html)

Interoffice

Answer 16

just use built-in gen_random_uuid () → uuid

As stated:

This function returns a version 4 (random) UUID. This is the most commonly used type of UUID and is appropriate for most applications.

https://www.postgresql.org/docs/current/functions-uuid.html

Polytechnic