9

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.

16

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;
  • Function to_regproc doesn't exist either: ERROR: function to_regproc(unknown) does not exist. – d9k Aug 8 '16 at 20:50
  • What version are you using? This was only recently added... Try the pg_proc query. – cohenjo Aug 8 '16 at 20:52
  • cohenjo, ok, I got it. select version() gave me PostgreSQL 9.3.12 on x86_64-unknown-linux-gnu... I upgraded Ubuntu from 14.04 to 16.04 but psql server wasn't upgraded properly, I think. psql --version really shows me 9.5.3, that confused me a lot. – d9k Aug 8 '16 at 20:59
  • I installed 9.5 postgresql version but it didn't replace existing 9.3 version, it just started alongside but on the other port! So there were two versions installed and I had to move and upgrade existing databases from 9.3 to 9.5 with this tutorial wiki.postgresql.org/wiki/Using_pg_upgrade_on_Ubuntu/Debian But even after this upgrade to make gen_random_uuid() work I had to manually recreate extension (drop extension pgcrypto; CREATE EXTENSION pgcrypto;) as you assumed. Thanks, @cohenjo! – d9k Aug 8 '16 at 23:45
  • I used CREATE IF NOT EXISTS and had this error. Consequent DROP EXTENSION pgcrypto and CREATE EXTENSION pgcrypto helped. Thank you. – Zon Mar 4 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.