gen_random_uuid() fails on an older PostgreSQL server
SELECT gen_random_uuid(); fails on a PostgreSQL 12 or older serverShort answer
gen_random_uuid() became a core function in PostgreSQL 13. On version 12 or older it exists only inside the pgcrypto extension, so the call fails until you create that extension in the current database. If pgcrypto is created but still not found, it sits in a schema outside your search_path.
Fix it fast
1Find out which cause you have
Print the server version and whether pgcrypto is installed in this database. The version decides which cause applies.
SHOW server_version; SELECT e.extname, n.nspname AS schema FROM pg_extension e JOIN pg_namespace n ON n.oid = e.extnamespace WHERE e.extname = 'pgcrypto';2Match what you see, then jump to its fix
- 1
server_version is 12 or lower and the second query returns no rowsThe server is older than 13 and pgcrypto is not createdFix - 2
The second query returns a row in another database, or shows a schema that is not on your search_pathpgcrypto exists, but not in the database or schema you are usingFix - 3
CREATE EXTENSION answers: extension "pgcrypto" is not availablepgcrypto is not installed on the server at allFix
- 1
What does the error look like?
SELECT gen_random_uuid(); fails on a PostgreSQL 12 or older serverA table default of gen_random_uuid() cannot be created
Cause 1 of 3
The server is older than 13 and pgcrypto is not created
1Check
The 13.0 release notes say the function was added to core in that release; before that UUID generation lived only in the uuid-ossp and pgcrypto modules.
SHOW server_version_num;2Fix
Upgrade to a supported version, or create pgcrypto in the database. pgcrypto is marked trusted, so a role with CREATE on the database can install it.
CREATE EXTENSION IF NOT EXISTS pgcrypto;3Verify
The function returns a version 4 UUID.
SELECT gen_random_uuid();Confirmed by postgresql.org
Cause 2 of 3
pgcrypto exists, but not in the database or schema you are using
1Check
Extension names are unique database-wide, so each database needs its own CREATE EXTENSION. Unqualified function names are found through the search path.
SELECT current_database();
SHOW search_path;2Fix
Create the extension in the right database, or call the function with its schema name and add that schema to the role's search path.
CREATE EXTENSION IF NOT EXISTS pgcrypto SCHEMA public;
-- or: SELECT extensions.gen_random_uuid();3Verify
Reconnect as the application role and call it without a schema.
SELECT gen_random_uuid();Confirmed by postgresql.org
Cause 3 of 3
pgcrypto is not installed on the server at all
1Check
The error hint says the extension must first be installed on the machine that runs PostgreSQL. pgcrypto needs OpenSSL support at build time, so a minimal build may not include it.
SELECT name, default_version FROM pg_available_extensions WHERE name = 'pgcrypto';2Fix
Install the extension files that ship with your server (the contrib modules in your distribution's PostgreSQL packages), then create it. Or move to PostgreSQL 13 or newer, where no extension is needed.
CREATE EXTENSION pgcrypto;3Verify
The first query now returns a row and the function works.
SELECT gen_random_uuid();Confirmed by postgresql.org
How do I stop it coming back?
- Run a supported PostgreSQL version; version 13 and later need no extension for random UUIDs.
- Put CREATE EXTENSION in migrations so every new database gets it.
- Use the same extension schema in every environment.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
The uuid-ossp extension is listed, but uuid_generate_v4() still fails
uuid_generate_v4() works in one database or role but not in another
The function lives in the schema where uuid-ossp was installed, and extensions are installed per database.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix