PostgreSQL 外部表を使ってCSVファイルへアクセスする

file_fdwモジュールを使いcsvファイルを直接selectします。

拡張モジュールインストールと確認

D:\>psql -U sooni -d myposdb -h vm022
psql (13.9、サーバ 13.6)
"help"でヘルプを表示します。

myposdb=# \dx
                                      インストール済みの拡張一覧
     名前     | バージョン |  スキーマ  |                             説明
--------------+------------+------------+--------------------------------------------------------------
 dblink       | 1.2        | public     | connect to other PostgreSQL databases from within a database
 plpgsql      | 1.0        | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0        | public     | foreign-data wrapper for remote PostgreSQL servers
(3 行)


-- file_fdwのインストール
--
myposdb=# CREATE EXTENSION file_fdw;
CREATE EXTENSION
myposdb=# \dx
                                      インストール済みの拡張一覧
     名前     | バージョン |  スキーマ  |                             説明
--------------+------------+------------+--------------------------------------------------------------
 dblink       | 1.2        | public     | connect to other PostgreSQL databases from within a database
 file_fdw     | 1.0        | sooni      | foreign-data wrapper for flat file access
 plpgsql      | 1.0        | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0        | public     | foreign-data wrapper for remote PostgreSQL servers
(4 行)


myposdb=#

外部サーバの定義

create server csv_server foreign data wrapper file_fdw
;

定義の確認

select s.oid,s.srvname
,array_to_string(s.srvacl,CHR(10)) as srvacl
,array_to_string(array (select (pg_options_to_table(s.srvoptions))),CHR(10)) srvoptions
from pg_catalog.pg_foreign_server s
;

-- 外部サーバの定義
--
myposdb=# create server csv_server foreign data wrapper file_fdw
myposdb-# ;
CREATE SERVER

-- 定義したサーバの確認
--
myposdb=# select s.oid,s.srvname
myposdb-# ,array_to_string(s.srvacl,CHR(10)) as srvacl
myposdb-# ,array_to_string(array (select (pg_options_to_table(s.srvoptions))),CHR(10)) srvoptions
myposdb-# from pg_catalog.pg_foreign_server s
myposdb-# ;
  oid  |   srvname   |       srvacl        |     srvoptions
-------+-------------+---------------------+---------------------
 16584 | prdb_server | postgres=U/postgres+| (host,192.168.3.22)+
       |             | sooni=U/postgres    | (port,5432)        +
       |             |                     | (dbname,prdb)
 17779 | csv_server  |                     |
(2 行)


myposdb=#

外部テーブルのcreate

今回のCSVは、先頭行がヘッダーになっているShift_JISファイルです。
そのため encoding ‘sjis’,header ‘true’ としています。

CREATE FOREIGN TABLE csv_sjis (
  row_id integer,
  lno varchar(2),
  lname varchar(20),
  ltime timestamp with time zone
) SERVER csv_server
OPTIONS ( filename '/var/lib/pgsql/csv/test_sjis.csv'
,format 'csv'
,header 'true'
,null ''
,encoding 'sjis')
;

外部テーブルのdrop

drop FOREIGN TABLE csv_sjis;

CSVファイル

row_id,lno,names,ltime
1,10,"テスト","2022-12-12 19:00:49"
2,30,"てすと","2022-12-23"
3,40,"１～２",
4,,"4行目NULL",2022-12-12 01:00
5,"","5行目空文字","2022/12/01 21:34:02"

csvファイルのselect結果

-- 未入力の項目はNULLになります。
-- ダブルクォーテーションで囲むと空文字扱いです
-- timestamp項目はある程度のフォーマットを許容してくれているので取り扱いが楽です
--
myposdb=# \pset null '(null)'
Null表示は"(null)"です。
myposdb=# select * from csv_sjis;
 row_id |  lno   |    lname    |         ltime
--------+--------+-------------+------------------------
      1 | 10     | テスト      | 2022-12-12 19:00:49+09
      2 | 30     | てすと      | 2022-12-23 00:00:00+09
      3 | 40     | １～２      | (null)
      4 | (null) | 4行目NULL   | 2022-12-12 01:00:00+09
      5 |        | 5行目空文字 | 2022-12-01 21:34:02+09
(5 行)

myposdb=#