PostgreSQL file_fdw的使用-CSDN博客

本文链接：https://blog.csdn.net/luojinbai/article/details/45673113

1. 简介
　　file_fdw模块提供了外部数据封装器file_fdw，可以用来在服务器的文件系统中访问数据文件。数据文件必须是COPY FROM 可读的格式；参阅COPY获取细节。访问这样的数据文件当前只是可读的。
　　简单的说，就是创建一个表，表的数据来源于数据库之外的文件，而这个文件是通过copy命令导出去的。

　　使用这个封装器创建的外部表可以有下列选项：

参数	说明
filename	指定要读取的文件。这是必需的。必须是一个绝对路径名。
format	指定文件的格式，与COPY的FORMAT选项相同。
header	指定文件是否有标题行，与COPY的HEADER选项相同。
delimiter	指定文件的分隔符，与COPY的DELIMITER选项相同。
quote	指定文件的引用字符，与COPY的QUOTE选项相同。
escape	指定文件的逃逸字符，与COPY的ESCAPE选项相同。
null	指定文件的null字符串，与COPY的NULL选项相同。
encoding	指定文件的编码，与COPY的ENCODING选项相同。

2. 添加file_fdw扩展。

postgres=# create extension file_fdw ;
CREATE EXTENSION
postgres=#
postgres=# \dx --查看所有的外部扩展
                            List of installed extensions
   Name   | Version |   Schema   |                   Description                    
----------+---------+------------+--------------------------------------------------
 file_fdw | 1.0     | public     | foreign-data wrapper for flat file access
 hstore   | 1.2     | public     | data type for storing sets of (key, value) pairs
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

3. 测试file_fdw

　3.1 创建测试表，添加测试数据

postgres=# create  table tb10(id integer,name character varying,passworld character varying);
CREATE TABLE
postgres=# 
postgres=# insert into tb10 select generate_series(1,50),'john',md5(random()::text);
INSERT 0 50

　3.2 通过copy拷贝成文件

postgres=# copy tb10 to '/home/postgres/tb10.csv';
COPY 50

　3.3 创建SERVER（外部服务器）

postgres=# create server server_file_fdw foreign data wrapper file_fdw;
CREATE SERVER

postgres=# \des
              List of foreign servers
      Name       |  Owner   | Foreign-data wrapper 
-----------------+----------+----------------------
 server_file_fdw | postgres | file_fdw
(1 row)

注：以 postgres 用户连接虚拟机上的库 mydb, 并创建 SERVER;Server 可以理解为外部服务器，是本地库访问外部数据的桥梁。

　3.4 创建外部表

postgres=# create foreign table foreign_tb10 (id integer,name character varying,password character varying)server server_file_fdw  options (filename '/home/postgres/tb10.csv');
CREATE FOREIGN TABLE

备注：这里创建外部表，并指定外部 Server 为 server_file_fdw，在测试过程中，发现外部表有很多限制,目前发现了以下：

default 值不支持；
索引不支持；

postgres=# \d foreign_tb10 
          Foreign table "public.foreign_tb10"
  Column  |       Type        | Modifiers | FDW Options 
----------+-------------------+-----------+-------------
 id       | integer           |           | 
 name     | character varying |           | 
 password | character varying |           | 
Server: server_file_fdw
FDW Options: (filename '/home/postgres/tb10.csv')

CREATE FOREIGN TABLE
postgres=# select * from foreign_tb10 order by id limit 10;
 id | name |             password             
----+------+----------------------------------
  1 | john | 40f328dd6d918fc2565325917e2c0fa5
  2 | john | 2fe543e9983e6fbbcbee8b78045c44fc
  3 | john | 9315889aac4098a7f26c948a6e3be3da
  4 | john | 0bf9dc150651eaa358568b94451a9796
  5 | john | 8c44c90724f5bdf4fd5da4e959e1700f
  6 | john | 0470c540c0273519607e63bedaf939ca
  7 | john | a84a0bfdb0f0712ff4d2fc418128aa02
  8 | john | 06acd231204e572dc96af794c4b8bc44
  9 | john | fe52c4a5e449d22a1339c0767efb56b6
 10 | john | c1f228300368b320192d0e6939f6ee80
(10 rows)

　3.5 执行计划

postgres=# explain select * from foreign_tb10 order by id limit 10;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Limit  (cost=3.55..3.58 rows=10 width=68)
   ->  Sort  (cost=3.55..3.61 rows=21 width=68)
         Sort Key: id
         ->  Foreign Scan on foreign_tb10  (cost=0.00..3.10 rows=21 width=68)
               Foreign File: /home/postgres/tb10.csv
               Foreign File Size: 2041
(6 rows)