DBA Hub

📋Steps in this guide1/5

How to access csv files on file system using file_fdw in postgres - DBACLASS DBACLASS

File_fdw is an extension which can be used to access flat files like csv in the server's file system. follow below steps. #create extension

postgresql configurationintermediate
by PostgreSQL
13 views
1

Overview

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: 2. Now create the extension:

Code/Command (click line numbers to comment):

1
2
3
4
5
edb=#   <strong><span style="color: #ff0000;">select * from pg_available_extensions where name='file_fdw';</span></strong>
   name   | default_version | installed_version |                  comment
----------+-----------------+-------------------+-------------------------------------------
 file_fdw | 1.0             | 1.0               | foreign-data wrapper for flat file access
(1 row)
2

Section 2

3. Now create a server: 4. prepare the csv file:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
edb=#<span style="color: #ff0000;"><strong> create extension file_fdw;</strong></span>
CREATE EXTENSION
edb=# <span style="color: #ff0000;"><strong>\dx</strong></span>
                                  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)

edb=# <span style="color: #ff0000;"><strong>create server file_server foreign data wrapper file_fdw;</strong></span>
CREATE SERVER

edb=# <span style="color: #ff0000;"><strong>\des+</strong></span>
                                              List of foreign servers
    Name     |    Owner     | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
-------------+--------------+----------------------+-------------------+------+---------+-------------+-------------
 file_server | enterprisedb | file_fdw             |                   |      |         |             |
(1 row)
3

Section 3

5. Now create the foreign table: 6. Try to access the foreign table:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-bash-4.2$ cat /var/lib/edb/test.csv
1, ram,  MANAGER
2, RAVI , HR
3, SAMUEL , IRC
4, ABDUL , LEAD
5, PABLO , MANAGER

edb=# <strong><span style="color: #ff0000;">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');</span></strong>
CREATE FOREIGN TABLE

edb=# <span style="color: #ff0000;"><strong>\det+</strong></span>
                                      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)
4

Section 4

Now change some data in file and see the output again. What will happen if we try to update the table???

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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)

-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)
5

Section 5

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:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
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)

Comments (0)

Please to add comments

No comments yet. Be the first to comment!