join on two databases using postgresql and dblink

Question

I have the following databases:

server_a {
    a_t1 (a_t1_a1, a_t1_a2);
    a_t2 (a_t2_a1, a_t2_a2);
}
server_b {
    b_t1 (b_t1_a1, b_t1_a2);
    b_t2 (b_t2_a1, b_t2_a2);
}

attribute 1 = serial not null (primary)
attribute 2 = character varying(50)

I have to Join a table in the first to the second database

Here is the sql i have presumed:

Select * from a_t1 full outer join dblink('dbname=server_b','select * from b_t1') 
where a_t1.a_t1_a1>1

however i am getting this error:

ERROR:  syntax error at or near "where"
LINE 1: ...in dblink('dbname=server_b','select * from b_t1') where a_t1...
                                                             ^
********** Error **********

ERROR: syntax error at or near "where"
SQL state: 42601
Character: 83

Pavunkumar · Accepted Answer · 2013-12-23 12:30:30Z

2

Try like this.

Make alias for remote table and perform the query.

Select * from a_t1 full outer join dblink('dbname=server_b','select * from b_t1')  as tmp_b_t1 ( a1 serial not null (primary) , a2 character varying(50) )
where a_t1.tmp_b_t1>1

answered Dec 23, 2013 at 12:30

Pavunkumar

5,36915 gold badges46 silver badges69 bronze badges

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

Collectives™ on Stack Overflow

join on two databases using postgresql and dblink

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related