Postgresql has added the External table access function since 9.1. This function allows the database to directly read files other than the database, such as csv or text files. DML is not supported for the moment. Postgresql has various plug-ins that can directly connect to various heterogeneous databases, such as Oracle_fdw, mysql_fdw, and file_fdw. It is very convenient for data migration, which is a strong performance of postgresql's scalability. This section describes how to use file_fdw of foreign data wrapper.
PostgreSQL 9.3 materialized view usage
Four tips for using PostgreSQL database date type
PostgreSQL deletes duplicate data rows in a table
PostgreSQL cache details
Compiling PostgreSQL on Windows
Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu
I. Environment:
OS: CentOS 6.3
DB: PostgreSQL 9.3
Ii. Use Process
1. Create an extension. Because it is not installed by default, you must manually create the extension for installation.
[postgres@kenyon ~]$ psqlpsql (9.3.0)Type "help" for help.postgres=# CREATE EXTENSION file_fdw;CREATE EXTENSIONpostgres=# \dx List of installed extensions Name | Version | Schema | Description ----------+---------+------------+------------------------------------------- file_fdw | 1.0 | public | foreign-data wrapper for flat file access plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language(2 rows)
2. Create an External table service interface and view
postgres=# create server pg_file_server foreign data wrapper file_fdw;CREATE SERVERpostgres=# \des List of foreign servers Name | Owner | Foreign-data wrapper ----------------+----------+---------------------- pg_file_server | postgres | file_fdw(1 row)
3. Create an External table with the same structure as the external file
Postgres = # create foreign table tab_area (id int, cname varchar (80), ename varchar (80), create_time varchar (30 )) server pg_file_server options (filename '/home/S/data1.csv', format 'csv', header on, delimiter ',', null '1 '); -- descriptions of parameters in options -- filename is followed by the file name and absolute path -- format is format, and csv is separated by commas, text indicates the tab-separated Method -- delimiter is the delimiter -- header indicates whether the data in the first row needs to be converted into null data. In this example, field 1 is converted to null.
4. upload an external file
[S @ kenyon ~] $ More data1.csv? ID, cname, ename, create_time1, zhejiang, zhejiang, 2013-01-012, hangzhou, hangzhou, 2013-01-023, beijing, beijing, 2012-09-124, default Value: 2013-09-105, sichuan, sichuan, 2012-12-126, nanjing, 7, Gansu, and
5. Query
Postgres = # select * from tab_area; id | cname | ename | create_time ---- + ------- + ---------- + ------------- | zhejiang | 2013-01-01 2 | hangzhou | 2013-01-02 3 | beijing | 2012-09-12 4 | default | 5 | sichuan | 2012-12-12 6 | nanjing | 7 | Gansu | 1998-12-12 (7 rows)
6. view external tables
postgres=# \d+ tab_area Foreign table "public.tab_area" Column | Type | Modifiers | FDW Options | Storage | Stats target | Description -------------+-----------------------+-----------+-------------+----------+--------------+------------- id | integer | | | plain | | cname | character varying(80) | | | extended | | ename | character varying(80) | | | extended | | create_time | character varying(10) | | | extended | | Server: pg_file_serverFDW Options: (filename '/home/postgres/data1.csv', format 'csv', delimiter ',', header 'true', "null" '1')Has OIDs: no
Next, let's take a look at the highlights of page 2nd:
PostgreSQL details: click here
PostgreSQL: click here