Error Creating pgcrypto Extension

From: Mark Hill <Mark(dot)Hill(at)sas(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Cc: Phil Rau <Phil(dot)Rau(at)sas(dot)com>, Michael King <Michael(dot)King(at)sas(dot)com>, Ashley Stephens <Ashley(dot)Stephens(at)sas(dot)com>
Subject: Error Creating pgcrypto Extension
Date: 2026-09-17 20:24:36
Message-ID: BYAPR05MB6632AA793C3F51F016FE752EEEB82@BYAPR05MB6632.namprd05.prod.outlook.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

We are trying to run this code in Postgres 18.6:
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA pg_catalog CASCADE;

and getting the error
postgres=# CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA pg_catalog CASCADE;
ERROR: function "gen_random_uuid" already exists with same argument types
CONTEXT: SQL statement "CREATE FUNCTION gen_random_uuid()
RETURNS uuid
AS '$libdir/pgcrypto', 'pg_random_uuid'
LANGUAGE C VOLATILE PARALLEL SAFE"
extension script file "pgcrypto--1.3.sql", near line 66

That's because pgcrypto-1.3.sql has the following 4 lines starting at line 66:
CREATE FUNCTION gen_random_uuid()
RETURNS uuid
AS 'MODULE_PATHNAME', 'pg_random_uuid'
LANGUAGE C VOLATILE PARALLEL SAFE;

which try to create the gen_random_uuid() function but since it already exists it throws an error.

1) Should pgcrytpo-1.3.sql be trying to create gen_random_uuid() if/when it already exists?

The pgcrypto.control file contains:
# pgcrypto extension
comment = 'cryptographic functions'
default_version = '1.4'
module_pathname = '$libdir/pgcrypto'
relocatable = true
trusted = true

2) given what's in pgcrypto.control should there be a pgcrypto-1.4.sql file?

NOTE: If I comment lines 66-69 in pgcrypto-1.3.sql the
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA pg_catalog CASCADE;
code completes successfully.

I'm hesitant to alter pgcrypto-1.3.sql. Thoughts?

Thanks, Mark

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2026-09-17 21:11:52 Re: Error Creating pgcrypto Extension
Previous Message lakshmi 2026-09-17 06:30:17 Extension - ai_image_search: Image Search Inside PostgreSQL