Thread: Should PostgresFDW ImportForeignSchema should import the remote table default expression?

Here is the test:

-- create database
postgres=# create database foo;
CREATE DATABASE
postgres=# \c foo
You are now connected to database "foo" as user "rushabh".
-- Create remote table with default expression
foo=# create table test ( a int , b int default 200 );
CREATE TABLE
foo=# \c postgres
You are now connected to database "postgres" as user "rushabh".
postgres=# 
postgres=# create extension postgres_fdw ;
CREATE EXTENSION
-- Create server and user mapping
postgres=# create server myserver FOREIGN DATA WRAPPER postgres_fdw options (dbname 'foo', port '6666');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER myserver;
CREATE USER MAPPING
-- Import foreign schema
postgres=# import foreign schema public from server myserver into public;
IMPORT FOREIGN SCHEMA

-- Foreign table got imported
postgres=# \d test
           Foreign table "public.test"
 Column |  Type   | Modifiers |    FDW Options    
--------+---------+-----------+-------------------
 a      | integer |           | (column_name 'a')
 b      | integer |           | (column_name 'b')
Server: myserver
FDW Options: (schema_name 'public', table_name 'test')

-- Try to insert row and assume that it will add default value for 'b' column 
postgres=# insert into test (a) values ( 10 );
INSERT 0 1

-- But guess what, I was wrong ???
postgres=# select * from test;
 a  | b 
----+---
 10 |  
(1 row)

Looking at the code of postgresImportForeignSchema it clear that its not
importing the default expression from the foreign table. But question is whether it should ?

inputs/thoughts ?

Regards,
Rushabh Lathia
Oh I just found out that IMPORT FOREIGN do have
import_default and import_not_null options.

Got the answer, sorry for noise.

On Wed, Feb 17, 2016 at 1:31 PM, Rushabh Lathia <rushabh.lathia@gmail.com> wrote:
Here is the test:

-- create database
postgres=# create database foo;
CREATE DATABASE
postgres=# \c foo
You are now connected to database "foo" as user "rushabh".
-- Create remote table with default expression
foo=# create table test ( a int , b int default 200 );
CREATE TABLE
foo=# \c postgres
You are now connected to database "postgres" as user "rushabh".
postgres=# 
postgres=# create extension postgres_fdw ;
CREATE EXTENSION
-- Create server and user mapping
postgres=# create server myserver FOREIGN DATA WRAPPER postgres_fdw options (dbname 'foo', port '6666');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER myserver;
CREATE USER MAPPING
-- Import foreign schema
postgres=# import foreign schema public from server myserver into public;
IMPORT FOREIGN SCHEMA

-- Foreign table got imported
postgres=# \d test
           Foreign table "public.test"
 Column |  Type   | Modifiers |    FDW Options    
--------+---------+-----------+-------------------
 a      | integer |           | (column_name 'a')
 b      | integer |           | (column_name 'b')
Server: myserver
FDW Options: (schema_name 'public', table_name 'test')

-- Try to insert row and assume that it will add default value for 'b' column 
postgres=# insert into test (a) values ( 10 );
INSERT 0 1

-- But guess what, I was wrong ???
postgres=# select * from test;
 a  | b 
----+---
 10 |  
(1 row)

Looking at the code of postgresImportForeignSchema it clear that its not
importing the default expression from the foreign table. But question is whether it should ?

inputs/thoughts ?

Regards,
Rushabh Lathia



--
Rushabh Lathia