dblink does not exist even when the extension alre

问题:

So I'm new to using dblink, I just created a script that inserts data into a table from another database. I received the error function dblink(unknown,unknown) does not exist.

So I checked online, and used CREATE EXTENSION dblink, ended up getting this message extension "dblink" already exists.

My dblink code is like this:

INSERT INTO tableA
 SELECT tbl.colA,tbl.colB,...
 FROM dblink('dbname=anotherDB', 'SELECT colA,colB,...
                 FROM tableB')
as tbl(colA,colB,...)

回答1:

Check out in which schema the extension is installed. In my case this schema is ext:

select nspname as schema
from pg_extension e
join pg_namespace n on n.oid = e.extnamespace
where extname = 'dblink'

 schema 
--------
 ext
(1 row) 

Add the schema name to the search path, e.g.:

set search_path to public, ext;

or use the qualified name of the function dblink(), e.g.:

INSERT INTO tableA
 SELECT tbl.colA,tbl.colB,...
 FROM ext.dblink('dbname=anotherDB', 'SELECT colA,colB,...
                 FROM tableB')
as tbl(colA,colB,...)