Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

The uuid-ossp extension is listed, but uuid_generate_v4() still fails

ErrorSELECT uuid_generate_v4(); fails although CREATE EXTENSION reported success

Short answer

The function lives in the schema where uuid-ossp was installed, and extensions are installed per database. A call fails when you are connected to a different database, when the extension sits in a schema that is not on your search_path, or when a column default was created before the extension. You can also skip the extension and call gen_random_uuid().

  • Applies to PostgreSQL 17 and 18 manual, Amazon RDS for PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    In the database and role your application really uses, list the extension and the schema its functions live in, and print the search path. The two answers show which cause applies.

    SELECT e.extname, n.nspname AS schema
    FROM pg_extension e JOIN pg_namespace n ON n.oid = e.extnamespace
    WHERE e.extname = 'uuid-ossp';
    
    SELECT current_database(), current_user;
    SHOW search_path;
  2. 2Match what you see, then jump to its fix

    1. 1The first query returns no rows here, although you saw CREATE EXTENSION succeed somewhereThe extension was created in a different databaseFix
    2. 2The first query shows a schema other than public (or one missing from search_path)The extension is in a schema that is not on the search_pathFix
    3. 3No special UUID flavour is required; the extension is only there for version 4 valuesYou only need random UUIDs, so the extension is not needed at allFix

What does the error look like?

  • SELECT uuid_generate_v4(); fails although CREATE EXTENSION reported success
  • uuid_generate_v4() works in one database or role but not in another

Cause 1 of 3

The extension was created in a different database

1Check

The manual says extensions have names that are unique database-wide, so each database needs its own CREATE EXTENSION. The current_database() value in the first step tells you where you are connected.

SELECT current_database();

2Fix

Create the extension in the database the application uses. The module is marked trusted, so a role with CREATE privilege on that database can install it.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

3Verify

The function now returns a value.

SELECT uuid_generate_v4();

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

The extension is in a schema that is not on the search_path

1Check

Unqualified function names are looked up through the search path in the same way as table names, and the extension's functions go into the schema chosen at install time. A different default schema or SET search_path in the application's role hides them.

SHOW search_path;
SELECT n.nspname FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = 'uuid_generate_v4';

2Fix

Call the function with its schema, or add that schema to the role's search path. Qualified names work for functions the same way as for tables.

SELECT extensions.uuid_generate_v4();
ALTER ROLE app_user SET search_path = "$user", public, extensions;

3Verify

Reconnect as the application role and call the function without a schema.

SELECT uuid_generate_v4();

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

You only need random UUIDs, so the extension is not needed at all

1Check

The uuid-ossp page itself says the module is only necessary for special requirements beyond what core PostgreSQL offers, and the manual lists gen_random_uuid() as a built-in way to make a version 4 UUID.

SELECT gen_random_uuid();

2Fix

Use the built-in function in new tables and in defaults. This removes the dependency on the extension and its schema.

ALTER TABLE users ALTER COLUMN id SET DEFAULT gen_random_uuid();

3Verify

Insert a row without an id and read it back.

INSERT INTO users DEFAULT VALUES RETURNING id;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Put CREATE EXTENSION in your migrations, so every new database gets it.
  • Install extensions in a schema that is on every application role's search_path, or always call with the schema.
  • Prefer gen_random_uuid() for plain random identifiers.

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.