0

Here is how I am doing it: 

CREATE EXTENSION IF NOT EXISTS postgres_fdw;
    DROP SERVER IF EXISTS myserver CASCADE;
    CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '10.1.1.1', dbname 'mydb', port '5432');
    DROP USER MAPPING IF EXISTS FOR user SERVER myserver;

        CREATE USER MAPPING FOR user
                SERVER myserver
                OPTIONS (user 'user', password 'password');

        CREATE FOREIGN TABLE IF NOT EXISTS foriegnemployee(
         id int,
        name text,
        is_done boolean
        )
          SERVER myserver
          OPTIONS (schema_name 'myschema', table_name 'employee');

When I run the following query, it says table mydb.employee does not not exist: 

Update foriegnemployee set is_done=true where id in (select id from sometable);

sometable is a local table. employee table is in a remote db

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
Tisha
  • 827
  • 3
  • 11
  • 34
  • `update foriegnemployee set is_done=true where id in (select id from sometable);` you meant?.. the rest looks ok. sure you connect to the right remote db?.. – Vao Tsun Nov 18 '16 at 11:22
  • I want to update remotedb.employee table such that the boolean column is_done is set to true for every id that is present in the local database table called sometable – Tisha Nov 18 '16 at 11:24
  • yes, so from your code `CREATE FOREIGN TABLE IF NOT EXISTS foriegnemployee...` it should be `update foriegnemployee set is_done=true where id in (select id from sometable);` - right? – Vao Tsun Nov 18 '16 at 11:25
  • Yes that was a typo. It is using foriegnemployee – Tisha Nov 18 '16 at 11:42

1 Answers1

1

I tried to mockup your tables:

t=# create database b;
CREATE DATABASE
t=# \c b
You are now connected to database "b" as user "vao".
b=#  create table employee (is_done boolean, user_id int);
CREATE TABLE
b=# insert into employee select false,1;
INSERT 0 1
b=# \c t
You are now connected to database "t" as user "vao".
t=# create extension postgres_fdw;
CREATE EXTENSION
t=# create server b foreign data wrapper postgres_fdw options (dbname 'b');
CREATE SERVER
t=# create user mapping for vao server b;
CREATE USER MAPPING
t=# create foreign table ftb (is_done boolean, user_id int) server b options (table_name 'employee');
CREATE FOREIGN TABLE
t=# Update ftb set is_done=true where user_id in (select user_id from employee);
UPDATE 1

code works. Either server or table have wrong options I suppose

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • I have a database called mydb, inside which I have a schema called myschema. Not sure whats wrong – Tisha Nov 18 '16 at 12:01
  • Oh got it, I had created it on a wrong server and while creating it again on the right server, it was saying it already exists. Phew!! Thanks @Vao Tsun – Tisha Nov 18 '16 at 12:03