This error occurs when PostgreSQL cannot find a required extension. The extension files may not be installed, or they are not available in the system. Most commonly, you need to install the extension using CREATE EXTENSION or ensure the required packages are installed on your system.
PostgreSQL extensions are optional packages that add functionality like full-text search (pg_trgm), UUID generation (uuid-ossp), or JSON processing (json). When PostgreSQL attempts to load or use an extension that is not installed on the system, it raises this error. This typically happens when: (1) the extension has never been installed on the server, (2) the system package containing the extension is missing, or (3) the extension files are not in the expected location. PostgreSQL stores available extensions in the pg_available_extensions system view, and the extension control files are usually located in the SHAREDIR/extension directory.
Connect to PostgreSQL and query the available extensions:
SELECT * FROM pg_available_extensions WHERE name = 'pg_trgm';If this returns no results, the extension is not available and you need to install the supporting files.
On most Linux systems, extensions come in a separate package. Install it:
Ubuntu/Debian:
sudo apt-get install postgresql-contribCentOS/RHEL:
sudo yum install postgresql-contribFedora:
sudo dnf install postgresql-contribAfter installation, restart PostgreSQL or reconnect to see the extensions.
Once the extension files are installed, create the extension in your database. Connect as a superuser (usually postgres):
psql -U postgres -d your_databaseThen create the extension:
CREATE EXTENSION IF NOT EXISTS pg_trgm;Use IF NOT EXISTS to avoid errors if the extension is already installed. Check that it was created:
\dxIf using AWS RDS, Google Cloud SQL, Azure Database, or similar:
1. Check if the extension is in the allowed list for your service
2. Some providers require enabling extensions via parameter groups or settings
3. Not all extensions are supported by managed services
4. If your required extension is unsupported, you may need to use a self-hosted PostgreSQL instance or choose an alternative extension
For example, AWS RDS allows you to create extensions directly if they are supported, but some extensions require additional setup or are not available at all.
After creating the extension, verify it is available:
\dxThis lists all installed extensions. You should see your extension (e.g., pg_trgm) listed.
You can now use the extension's functions and data types:
CREATE INDEX idx_gin_text ON my_table USING gin(column_name gin_trgm_ops);Schema considerations: Extensions can be created in specific schemas. By default, they install into the public schema, but you can specify a schema using CREATE EXTENSION ... SCHEMA schema_name; if needed. Some extensions like timescaledb require additional PostgreSQL configuration (shared_preload_libraries) and must be set at server startup. If the extension appears installed (visible in pg_extension) but functions are not found, check your search_path setting with SHOW search_path; and ensure the extension's schema is included. For security-sensitive deployments, only trusted extensions should be installed.
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL
SSL error: certificate does not match host name
SSL error: certificate does not match host name in PostgreSQL
No SSL connection
No SSL connection to PostgreSQL