Extension exists but uuid_generate_v4 fails

162,490

Solution 1

The extension is available but not installed in this database.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Solution 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.

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

Solution 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,

Solution 5

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

public.uuid_generate_v4()
Share:
162,490

Related videos on Youtube

Incerteza
Author by

Incerteza

Alex is here: https://gildedhonour.co

Updated on November 11, 2021

Comments

  • Incerteza
    Incerteza over 2 years

    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?

    • Craig Ringer
      Craig Ringer over 10 years
      In future, please always show the exact text of any error message.
    • Athanassios
      Athanassios about 3 years
      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)
  • boatcoder
    boatcoder almost 8 years
    The command should be CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
  • ElementalStorm
    ElementalStorm almost 6 years
    Just to be clear, to select the db one can write \c <db name> in the pgsql console
  • Abhishek Mani
    Abhishek Mani about 5 years
    @CraigRinger Where I can find this doc?
  • Danish
    Danish over 3 years
    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.
  • Athanassios
    Athanassios about 3 years
    Yes I had that problem too and what you describe here fixed it
  • Joachim Rives
    Joachim Rives over 2 years
    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.