Using external tables in PostgreSQL

1. Installing FILE_FDW

Need to install FILE_FDW, usually into the PostgreSQL source package in the CONTRIB/FILE_FDW directory, execute:

makemake install

Then go into the database and execute the following SQL to install the FILE_FDW:

CREATE EXTENSION file_fdw;CREATE SERVER file_fdw_server FOREIGN DATA WRAPPER file_fdw;

2. Create an external table

Take the common EMP table as an example;Create foreign table EMP(empno integer NOT NULL,ename VARCHAR (Ten),Job VARCHAR (9),Mgr Integer,hiredate DATE,Sal NUMERIC (6,2),Comm NUMERIC (6,2),deptno integer) SERVER File_fdw_serverOPTIONS (format ' CSV ', header ' true ', filename '/home/postgres/emp.log ', delimiter ', ', null '); 

--后面options里面参数的说明
--filename后面是文件名和绝对路径
--format是格式,csv是逗号分隔,text表示是tab分隔的方式
--delimiter是分隔符--header表示第一行数据是否需要;为true表示不需要；默认为false；为需要
--null表示空数据的转化处理,例子中字段‘‘将转化为null

mydb=# select * from emp; empno | ename  |     job    | mgr  |  hiredate  |   sal    |  comm   | deptno -------+--------+-----------+------+-- ----------+---------+---------+--------  7369 | SMITH  | CLERK      | 7902 | 1980-12-17 |  800.00 |          |     20  7499 | allen  |  salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00  |     30  7521 | ward   | salesman   | 7698 | 1981-02-22 | 1250.00 |  500.00 |      30  7566 | jones  | manager   | 7839 | 1981-04-02  | 2975.00 |         |     20   7654 | martin | salesman  | 7698 | 1981-09-28 |  1250.00 | 1400.00 |     30  7698 | blake   | manager   | 7839 | 1981-05-01 | 2850.00 |          |     30  7782 |  clark  | manager   | 7839 | 1981-06-09 | 2450.00  |         |     10  7788  | SCOTT  | ANALYST   | 7566 | 1987-03-20 |  3000.00 | &nbsP;       |     20  7839 | king    | PRESIDENT |      | 1981-11-17 |  5000.00 |         |     10   7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 |  1500.00 |    0.00 |     30  7876 |  adams  | clerk     | 7788 | 1987-04-23 |  1100.00 |         |     20   7900 | JAMES  | CLERK     | 7698 |  1981-12-03 |  950.00 |         |      30  7902 | ford   | analyst   | 7566 | 1981-12-03  | 3000.00 |         |      20  7934 | miller | clerk     | 7782 |  1982-01-23 | 1300.00 |         |      10 (14 rows)   Through this can be very good to log analysis, of course, the direct script can also be very well implemented, but this also provides a way. --Literature  http://blog.osdba.net/521.html

Using external tables in PostgreSQL