PostgreSQL says function dblink(unknown, unknown) does not exist
ERROR: function dblink(unknown, unknown) does not existShort answer
dblink is an extension, not a built-in function. The error means it is not registered in the database you are connected to, or its schema is not on search_path. Run CREATE EXTENSION dblink in that database, install the contrib package if the command fails, and add a column list to every call.
Fix it fast
1Find out which cause you have
Check whether dblink is registered in the database you are connected to, and what search_path is.
SELECT current_database(); SELECT extname, extnamespace::regnamespace FROM pg_extension WHERE extname = 'dblink'; SHOW search_path;2Match what you see, then jump to its fix
- 1
The pg_extension query returns no row.The extension was never created in this databaseFix - 2
pg_extension shows dblink, but extnamespace is not public or a schema listed in SHOW search_path.The extension lives in a schema that is not on search_pathFix - 3
CREATE EXTENSION itself fails, so the extension never appears.CREATE EXTENSION cannot run for your role or the package is missingFix
- 1
What does the error look like?
ERROR: function dblink(unknown, unknown) does not exist
Cause 1 of 3
The extension was never created in this database
1Check
CREATE EXTENSION registers the new objects in the current database only, so it must be run in every database that uses it. Installing it in one database does nothing for another.
SELECT current_database();2Fix
Connect to the database that runs the query and create the extension.
CREATE EXTENSION IF NOT EXISTS dblink;3Verify
A test call works. Note the column list after AS; dblink returns records, so you must declare the columns.
SELECT * FROM dblink('dbname=postgres', 'select 1') AS t(n int);Confirmed by postgresql.org
Cause 2 of 3
The extension lives in a schema that is not on search_path
1Check
An unqualified name is only found in schemas on the search path. If the extension was installed with SCHEMA somewhere else, the plain call fails.
SHOW search_path;2Fix
Call the function with its schema name, or add the schema to the path for the session.
SELECT * FROM extensions.dblink('dbname=postgres', 'select 1') AS t(n int);
-- or
SET search_path TO public, extensions;3Verify
The call without errors.
SELECT * FROM dblink('dbname=postgres', 'select 1') AS t(n int);Confirmed by postgresql.org
Cause 3 of 3
CREATE EXTENSION cannot run for your role or the package is missing
1Check
CREATE EXTENSION needs a superuser unless the extension is trusted; dblink is not in the documented list of trusted extensions. On Linux distributions the module normally ships in a separate contrib package.
SELECT rolsuper FROM pg_roles WHERE rolname = current_user;2Fix
Ask a superuser to run the command in your database, and install the contrib package on the server if the files are missing.
sudo apt install postgresql-contrib
sudo -u postgres psql -d my_database -c "CREATE EXTENSION IF NOT EXISTS dblink;"3Verify
The extension now shows up in pg_extension.
SELECT extname FROM pg_extension WHERE extname = 'dblink';Confirmed by postgresql.org
How do I stop it coming back?
- Put CREATE EXTENSION IF NOT EXISTS in the migration that first uses dblink, not in a manual step.
- Install extensions in a known schema, and put it on the role's search_path.
- For new code, look at postgres_fdw, which the dblink page names as the more modern alternative.
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
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
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fixDatabases
manage.py test stops with 'permission denied to create database' on PostgreSQL
django.db.utils.ProgrammingError: permission denied to create database
Django creates a separate database called test_ plus your database name, using the DATABASES USER.
2 causes, each with a fixDatabases
An EC2 instance cannot reach its RDS database
Could not open connection to the host, on port <port>: Connect failed
RDS denies all access until a security group allows it.
3 causes, each with a fixDatabases
IntelliJ IDEA Test Connection fails for a PostgreSQL data source
Connection to localhost:5432 refused.
When Test Connection fails, work from the bottom up.
4 causes, each with a fixDatabases
pg_dump refuses to run: server version is newer than pg_dump
pg_dump: detail: server version: 17.2; pg_dump version: 14.13
pg_dump will not dump a server whose major version is newer than its own.
3 causes, each with a fix