Use of External tables in PostgreSQL

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.

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.

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.

Related reading:

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.

[S @ kenyon ~] $ Psqlpsql (9.3.0) Type "help" for help. S = # create extension file_fdw; CREATE extensionipvs =#\ dxList of installed extensionsName | Version | Schema | Description ---------- + --------- + ------------ + export 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 =#\ desList of foreign serversName | Owner | Foreign-data wrapper ---------------- + ---------- + ---------------------- pg_file_server | S | 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

S = # \ d + tab_areaForeign table "public. tab_area "Column | Type | Modifiers | FDW Options | Storage | Stats target | Description --------------- + tables + ----------- + ------------- + ---------- + ------------ + ------------- 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/S/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

,