File_fdw is an extension which can be used to access flat files like csv in the server’s file system.
Lets see the below DEMO:
1. Check whether file_fdw extension is available on server or not:
edb=# select * from pg_available_extensions where name='file_fdw';
name | default_version | installed_version | comment
----------+-----------------+-------------------+-------------------------------------------
file_fdw | 1.0 | 1.0 | foreign-data wrapper for flat file access
(1 row)
2. Now create the extension:
edb=# create extension file_fdw;
CREATE EXTENSION
edb=# \dx
List of installed extensions
Name | Version | Schema | Description
------------------+---------+------------+------------------------------------------------------
edb_dblink_libpq | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper for PostgreSQL
edb_dblink_oci | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper for Oracle
edbspl | 1.0 | pg_catalog | EDB-SPL procedural language
file_fdw | 1.0 | public | foreign-data wrapper for flat file access --- >>>> this one
pldbgapi | 1.1 | pg_catalog | server-side support for debugging PL/pgSQL functions
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(6 rows)
3. Now create a server:
edb=# create server file_server foreign data wrapper file_fdw;
CREATE SERVER
edb=# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
-------------+--------------+----------------------+-------------------+------+---------+-------------+-------------
file_server | enterprisedb | file_fdw | | | | |
(1 row)
4. prepare the csv file:
-bash-4.2$ cat /var/lib/edb/test.csv
1, ram, MANAGER
2, RAVI , HR
3, SAMUEL , IRC
4, ABDUL , LEAD
5, PABLO , MANAGER
5. Now create the foreign table:
edb=# create foreign table file_test ( empid int, emp_name varchar , dept_name varchar) server file_server options (filename '/var/lib/edb/test.csv',format 'csv');
CREATE FOREIGN TABLE
edb=# \det+
List of foreign tables
Schema | Table | Server | FDW options | Description
--------+-----------+-------------+--------------------------------------------------+-------------
public | file_test | file_server | (filename '/var/lib/edb/test.csv', format 'csv') |
(1 row)
6. Try to access the foreign table:
edb=# select * from file_test;
empid | emp_name | dept_name
-------+----------+-----------
1 | ram | MANAGER
2 | RAVI | HR
3 | SAMUEL | IRC
4 | ABDUL | LEAD
5 | PABLO | MANAGER
(5 rows)
Now change some data in file and see the output again.
-bash-4.2$ cat /var/lib/edb/test.csv
100,ram,MANAGER
200,RAVI,HR
300,SAMUEL,IRC
400,ABDUL,LEAD
500,PABLO,MANAGER
edb=# select * from file_test;
empid | emp_name | dept_name
-------+----------+-----------
100 | ram | MANAGER
200 | RAVI | HR
300 | SAMUEL | IRC
400 | ABDUL | LEAD
500 | PABLO | MANAGER
(5 rows)
What will happen if we try to update the table???
edb=# delete from file_test;
ERROR: cannot delete from foreign table “file_test”
i.e these tables cannot be changed using insert/update/delete command. these are read only tables.
check the explain plan:
edb=# explain analyze select * from file_test;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Foreign Scan on file_test (cost=0.00..1.10 rows=1 width=68) (actual time=0.049..0.059 rows=5 loops=1)
Foreign File: /var/lib/edb/test.csv
Foreign File Size: 76 b
Planning Time: 0.186 ms
Execution Time: 0.213 ms
(5 rows)
