Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

gen_random_uuid() fails on an older PostgreSQL server

ErrorSELECT gen_random_uuid(); fails on a PostgreSQL 12 or older server

Short 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.

  • Applies to PostgreSQL 12 and older (pgcrypto), PostgreSQL 13 to 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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';
  2. 2Match what you see, then jump to its fix

    1. 1server_version is 12 or lower and the second query returns no rowsThe server is older than 13 and pgcrypto is not createdFix
    2. 2The 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. 3CREATE EXTENSION answers: extension "pgcrypto" is not availablepgcrypto is not installed on the server at allFix

What does the error look like?

  • SELECT gen_random_uuid(); fails on a PostgreSQL 12 or older server
  • A 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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.