psql 9.5: gen_random_uuid() not working

SELECT gen_random_uuid()

produces output

ERROR:  function gen_random_uuid() does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

I ran CREATE EXTENSION pgcrypto; on selected database and SELECT gen_random_bytes(1) works perfectly (gen_random_bytes doesn't work on other databases where pgcrypto extension was not manually created).

% psql --version
psql (PostgreSQL) 9.5.3

Ubuntu version is 16.04.

Answers 1

  • you can check if the functions are defined using

    select pg_get_functiondef(to_regproc('gen_random_bytes'));
    select pg_get_functiondef(to_regproc('gen_random_uuid'));
    

    or:

    select * from pg_proc where proname like 'gen_random_%';
    

    if both functions are not defined then you probably had an error with the extension creation - just drop it and recreate:

    drop extension pgcrypto;
    CREATE EXTENSION pgcrypto;
    

Related Questions