Extension exists but uuid generate v4 fails

Question

At amazon ec2 RDS Postgresql     gt  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

User · Accepted Answer

The extension is available but not installed in this database   CREATE EXTENSION IF NOT EXISTS  uuid-ossp

User · Answer

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

User · Answer

If you ve changed the search path  specify the public schema in the function call  public uuid generate v4

User · Answer

1 Re-install uuid-ossp extention in an exact schema  SET search path TO public  DROP EXTENSION IF EXISTS  quot uuid-ossp quot    CREATE EXTENSION  quot uuid-ossp quot  SCHEMA public   If this is a fresh installation you can skip SET and DROP  Credits to  atomCode  details  After this  you should see uuid generate v4   function IN THE RIGHT SCHEMA  when execute  df query in psql command-line prompt    2 Use fully-qualified names  with schemaname  qualifier   CREATE TABLE public my table       id uuid DEFAULT public uuid generate v4   NOT NULL

User · Answer

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

User · Answer

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

User · Answer

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

[postgresql] Extension exists but uuid_generate_v4 fails

Examples related to postgresql

Examples related to amazon-web-services

Examples related to amazon-ec2