PostgreSQL 9.5: Creating foreign key across database using Foreign Data Wrapper (FDW)

1.4k views Asked by At

I want to create foreign key in the current database table by referring other database table.

So i have decided to go with FDW.

I have done the following steps:

Step 1: Created extension

CREATE EXTENSION postgres_fdw;

Step 2: Created foreign server

CREATE SERVER foreign_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '127.0.0.1', port '5432', dbname 'testing');

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

Step 3: Created foreign table

CREATE FOREIGN TABLE fgn_tbl_calculator 
(
  value1 integer,
  value2 integer,
  sum integer,
  sub integer,
  div integer,
  mul integer
)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'calculator');

Step 4: Creating table with foreign key

create table calculator_copy
(
  value1 integer,
  value2 integer,
  sum integer,
  sub integer,
  div integer,
  mul integer,
  primary key (value1),
  foreign key (value1) references fgn_tbl_calculator(value1)
);

But stuck with an error:

 ERROR:  referenced relation "fgn_tbl_calculator" is not a table
0

There are 0 answers