How to use PostgreSQL Foreign Data Wrapper to join 2 different postgresql databases

Question

Can anyone provide an example (with the various SQL statements involved) on how to use foreign data wrappers in postgresql to enable a table from a postgresql database A to be joined to a table from a postgresql database B?

It is unclear from the docs to what degree is the FDW functionality available in pgsql 9.0 versus 9.1. The docs also do not have any examples that shows how to join between 2 different postgresql databases (with WHERE qualifier push-down) using FDW.

http://www.postgresql.org/docs/9.0/static/sql-createforeigndatawrapper.html

http://www.postgresql.org/docs/9.1/static/ddl-foreign-data.html

http://www.depesz.com/index.php/2011/03/14/waiting-for-9-1-foreign-data-wrapper/

Denis de Bernardy · Accepted Answer · 2011-07-17 07:03:12Z

7

You manipulate it just like any table. Per Depesz' post:

CREATE FOREIGN TABLE passwd (
    username text,
    pass text,
    uid int4,
    gid int4,
    gecos text,
    home text,
    shell text
) SERVER file_server
OPTIONS (format 'text', filename '/etc/passwd', delimiter ':', null '');

select * from passwd;

The docs have no examples of joining tables for a good reason: it's plain old SQL...

The join push-down currently is the subject of a GSOC:

answered Jul 17, 2011 at 7:03

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

archmeta Over a year ago

The example you provided is for 'file_server'. Can you edit the example to connect to an external postgresql server? Thanks.

Denis de Bernardy Over a year ago

It'll then be the same kind of syntax with a different server. See the relevant docs here: postgresql.org/docs/current/static/sql-createserver.html / CREATE SERVER myserver FOREIGN DATA WRAPPER pgsql OPTIONS (host 'foo', dbname 'foodb', port '5432');

alfonx Over a year ago

I get the error "ERROR: foreign-data wrapper "pgsql" does not exist" on my 9.1 - Any ideas what I am missing? Thanks

alfonx · Accepted Answer · 2013-02-12 22:19:50Z

The simplest solution I found is the dblink extension. I tested it on PostgreSQL 9.1:

create extension dblink.
select * from dblink('port=5452 host=localhost dbname=mydb user=myuser password=xxx', 
                     'select id,spaltenname from variablen') as v (a int, b varchar(20));

http://www.postgresql.org/docs/9.1/static/dblink.html

A simple join would be:

with a as (select * from dblink('port=5452 host=localhost dbname=mydb user=myuser password=xxx', 'select id,spaltenname from variablen') as v (a int, b varchar(20)))
select a join (select 1)b on (true);

The example above enables you to join with a table on another postgresql server, but it is just a copy and then join. No automatic "WHERE qualifier push-down" as you called it. You could of course just select the lines WHERE you need them in the first statement...

jgoge · Accepted Answer · 2013-05-07 23:55:35Z

1

If you want to join 2 different postgresql databases I recommend you to use dblink:

select datos.* 
     FROM dblink('hostaddr=192.168.0.10 port=5432 dbname=my_dbname user=my_user password=my_pass'::text, ' 
               select field_1, field_2 
               from my_table  order by field_1
               '
               ::text) 
      datos(field_1, integer, field_2 character varying(10));

(I tested it on PostgreSQL 9.1.3) http://www.postgresql.org/docs/9.2/static/contrib-dblink-function.html

edited May 7, 2013 at 23:55

answered May 7, 2013 at 23:43

jgoge

1211 silver badge4 bronze badges

Collectives™ on Stack Overflow

How to use PostgreSQL Foreign Data Wrapper to join 2 different postgresql databases

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related