test_copy_from.sql

--0. COPY FROM insertion into the partitioned table(parition is foreign table).
--create table on external cluster
create table remote_child0_1(a int, b int, c int);
create table remote_child0_2(a int, b int, c int) ;
create table remote_child0_3(a int, b int, c int);
create table remote_child0_4(a int, b int, c int) ;
create table remote_child0_5(a int, b int, c int);

--create foreign table on local cluster
--Note:first, if postgres_fdw is not installed, should make,make install postgres_fdw under path postgresql/contrib/postgres_fdw/
CREATE EXTENSION postgres_fdw;
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (port '54321', dbname 'postgres');
CREATE USER MAPPING FOR postgres SERVER foreign_server OPTIONS (user 'postgres');

create table local_table0(a int, b int, c int) partition by range(a);
create foreign table local_child0_1 partition of local_table0 for values from (1) to (10000001) SERVER foreign_server OPTIONS (table_name 'remote_child0_1');
create foreign table local_child0_2 partition of local_table0 for values from (10000001) to (20000001) SERVER foreign_server OPTIONS (table_name 'remote_child0_2');
create foreign table local_child0_3 partition of local_table0 for values from (20000001) to (30000001) SERVER foreign_server OPTIONS (table_name 'remote_child0_3');
create foreign table local_child0_4 partition of local_table0 for values from (30000001) to (40000001) SERVER foreign_server OPTIONS (table_name 'remote_child0_4');
create foreign table local_child0_5 partition of local_table0 for values from (40000001) to (50000001) SERVER foreign_server OPTIONS (table_name 'remote_child0_5');

--execute copy from command
copy local_table0 from './parent_5000w.data';

--1. COPY FROM insertion into the partitioned table(parition is foreign partition).
-----create partitioned table on external cluster-----
create table remote_table(a int, b int, c int) partition by range(a);
create table remote_child1 partition of remote_table for values from (1) to (10000001);
create table remote_child2 partition of remote_table for values from (10000001) to (20000001) ;
create table remote_child3 partition of remote_table for values from (20000001) to (30000001) ;
create table remote_child4 partition of remote_table for values from (30000001) to (40000001) ;
create table remote_child5 partition of remote_table for values from (40000001) to (50000001) ;

--create partitioned table with foreign partitions on local cluster
create table local_table(a int, b int, c int) partition by range(a);
create foreign table local_child1 partition of local_table for values from (1) to (10000001) SERVER foreign_server OPTIONS (table_name 'remote_child1');
create foreign table local_child2 partition of local_table for values from (10000001) to (20000001) SERVER foreign_server OPTIONS (table_name 'remote_child2');
create foreign table local_child3 partition of local_table for values from (20000001) to (30000001) SERVER foreign_server OPTIONS (table_name 'remote_child3');
create foreign table local_child4 partition of local_table for values from (30000001) to (40000001) SERVER foreign_server OPTIONS (table_name 'remote_child4');
create foreign table local_child5 partition of local_table for values from (40000001) to (50000001) SERVER foreign_server OPTIONS (table_name 'remote_child5');

--execute copy from command
copy local_table from './parent_5000w.data';

--2. COPY FROM insertion into the foreign table(without partition).
--create table on external cluster
create table remote_table2(a int, b int, c int);

--create foreign table on local cluster
create foreign table local_table2(a int, b int, c int) SERVER foreign_server OPTIONS (table_name 'remote_table2');

--execute copy from command
copy local_table2 from './parent_5000w.data';

--3. COPY FROM insertion into the partitioned table(part of foreign partitions).
--create partitioned table on external cluster-----
create table remote_table3(a int, b int, c int) partition by range(a);
create table remote_child3_1 partition of remote_table3 for values from (2000001) to (10000001);
create table remote_child3_2 partition of remote_table3 for values from (10000001) to (20000001) ;
create table remote_child3_3 partition of remote_table3 for values from (20000001) to (30000001) ;
create table remote_child3_4 partition of remote_table3 for values from (30000001) to (40000001) ;
create table remote_child3_5 partition of remote_table3 for values from (40000001) to (50000001) ;

--create foreign partitioned table on local cluster
create table local_table3(a int, b int, c int) partition by range(a);
create table local_child3_0 partition of local_table3 for values from (1) to (2000001);
create foreign table local_child3_1 partition of local_table3 for values from (2000001) to (10000001) SERVER foreign_server OPTIONS (table_name 'remote_child3_1');
create foreign table local_child3_2 partition of local_table3 for values from (10000001) to (20000001) SERVER foreign_server OPTIONS (table_name 'remote_child3_2');
create foreign table local_child3_3 partition of local_table3 for values from (20000001) to (30000001) SERVER foreign_server OPTIONS (table_name 'remote_child3_3');
create foreign table local_child3_4 partition of local_table3 for values from (30000001) to (40000001) SERVER foreign_server OPTIONS (table_name 'remote_child3_4');
create foreign table local_child3_5 partition of local_table3 for values from (40000001) to (50000001) SERVER foreign_server OPTIONS (table_name 'remote_child3_5');
--execute copy from command
copy local_table3 from './parent_5000w.data';

--4. COPY FROM insertion into the partitioned table with constraint.
--create table on external cluster
create table remote_table4(a int primary key, b int, c int) partition by range(a);
create table remote_child4_1 partition of remote_table4 for values from (2000001) to (10000001);
create table remote_child4_2 partition of remote_table4 for values from (10000001) to (20000001) ;
create table remote_child4_3 partition of remote_table4 for values from (20000001) to (30000001) ;
create table remote_child4_4 partition of remote_table4 for values from (30000001) to (40000001) ;
create table remote_child4_5 partition of remote_table4 for values from (40000001) to (50000001) ;

--create foreign table on local cluster
create table local_table4(a int, b int, c int) partition by range(a);
create table local_child4_0 partition of local_table4 for values from (1) to (2000001);
create foreign table local_child4_1 partition of local_table4 for values from (2000001) to (10000001) SERVER foreign_server OPTIONS (table_name 'remote_child4_1');
create foreign table local_child4_2 partition of local_table4 for values from (10000001) to (20000001) SERVER foreign_server OPTIONS (table_name 'remote_child4_2');
create foreign table local_child4_3 partition of local_table4 for values from (20000001) to (30000001) SERVER foreign_server OPTIONS (table_name 'remote_child4_3');
create foreign table local_child4_4 partition of local_table4 for values from (30000001) to (40000001) SERVER foreign_server OPTIONS (table_name 'remote_child4_4');
create foreign table local_child4_5 partition of local_table4 for values from (40000001) to (50000001) SERVER foreign_server OPTIONS (table_name 'remote_child4_5');
--execute copy from command
copy local_table4 from './parent_5000w.data';

--5. COPY FROM insertion into the foreign table with constraint.
--create table on external cluster
create table remote_table5(a int primary key, b int, c int);

--create foreign table on local cluster
create foreign table local_table5(a int, b int, c int) SERVER foreign_server OPTIONS (table_name 'remote_table5');

--execute copy from command
copy local_table5 from './parent_5000w.data';

--6. \copy insertion into the partitioned table with constraint.
--create table on external cluster
create table remote_table6(a int primary key, b int, c int) partition by range(a);
create table remote_child6_1 partition of remote_table6 for values from (2000001) to (10000001);
create table remote_child6_2 partition of remote_table6 for values from (10000001) to (20000001) ;
create table remote_child6_3 partition of remote_table6 for values from (20000001) to (30000001) ;
create table remote_child6_4 partition of remote_table6 for values from (30000001) to (40000001) ;
create table remote_child6_5 partition of remote_table6 for values from (40000001) to (50000001) ;

--create foreign table on local cluster
create table local_table6(a int, b int, c int) partition by range(a);
create table local_child6_0 partition of local_table6 for values from (1) to (2000001);
create foreign table local_child6_1 partition of local_table6 for values from (2000001) to (10000001) SERVER foreign_server OPTIONS (table_name 'remote_child6_1');
create foreign table local_child6_2 partition of local_table6 for values from (10000001) to (20000001) SERVER foreign_server OPTIONS (table_name 'remote_child6_2');
create foreign table local_child6_3 partition of local_table6 for values from (20000001) to (30000001) SERVER foreign_server OPTIONS (table_name 'remote_child6_3');
create foreign table local_child6_4 partition of local_table6 for values from (30000001) to (40000001) SERVER foreign_server OPTIONS (table_name 'remote_child6_4');
create foreign table local_child6_5 partition of local_table6 for values from (40000001) to (50000001) SERVER foreign_server OPTIONS (table_name 'remote_child6_5');
--execute copy from command
\copy local_table6 from './parent_5000w.data';