Use of External tables in PostgreSQL

Source: Internet
Author: User

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

  • 1
  • 2
  • Next Page

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.