Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

PostgreSQL says function dblink(unknown, unknown) does not exist

ErrorERROR: function dblink(unknown, unknown) does not exist

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

  • Applies to PostgreSQL 15, PostgreSQL 16, PostgreSQL 17, Ubuntu 24.04
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

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

    1. 1The pg_extension query returns no row.The extension was never created in this databaseFix
    2. 2pg_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. 3CREATE EXTENSION itself fails, so the extension never appears.CREATE EXTENSION cannot run for your role or the package is missingFix

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

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