psql 9.5: gen_random_uuid() not working

Question

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.

score 37 · Accepted Answer · answered Aug 08 '16 at 19:59

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;

psql 9.5: gen_random_uuid() not working

1 Answers1