PostgreSQL Lab : Load table from csv compress file using file_fdw

Step 1. Create Test Table

postgres=# create table t1 (c1 int, c2 varchar(200));
postgres=# insert into t1 values (1, 'TTTTTT');
postgres=# insert into t1 values (2, 'SSSSSS');
postgres=# insert into t1 values (3, 'RRRRRR');
postgres=# insert into t1 values (4, 'LLLLLL');
postgres=# insert into t1 values (5, 'OOOOOO');

Step 2. export to csv and compress with gzip

postgres=# copy (select * from t1) to PROGRAM 'gzip > /home/postgres/t1.csv.gz' delimiter '^' csv header ;

[postgres@pvdb ~]$ ls -l ~/*.gz
-rw------- 1 postgres postgres 53 Jan  9 10:51 /home/postgres/t1.csv.gz

Step 3. Create extension file_fdw and server

postgres=# create extension file_fdw ;
postgres=# create server t1 foreign data wrapper file_fdw ;

Step 4. Load compress csv file from program

postgres=# create foreign table ft_t1(c1 int, c2 varchar(200)) server t1 options (program 'gunzip -c /home/postgres/t1.csv.gz', format 'csv', delimiter '^', header 'on');

Step 5. Query foreign table

postgres=# select * from ft_t1;
 c1 |   c2
----+--------
  1 | TTTTTT
  2 | SSSSSS
  3 | RRRRRR
  4 | LLLLLL
  5 | OOOOOO
(5 rows)