トップ > PostgreSQL > 「PostgreSQL」COPY コマンドのバイナリ形式ファイルのフォーマットについて

「PostgreSQL」COPY コマンドのバイナリ形式ファイルのフォーマットについて

PostgreSQL

PostgreSQL COPY コマンドで出力できるバイナリ形式ファイル ( format binary 指定 ) のフォーマットについてメモ。

・COPY
- バイナリ形式
https://www.postgresql.jp/document/9.5/html/sql-copy.html#idp49533392

ドキュメントに記載があるようにファイルヘッダ、タプル、トレーラという要素から構成されるようです。

ファイルヘッダ

ファイルヘッダはさらに署名、フラグフィールド ( これら二つで 15 ビット )、ヘッダ拡張領域長というので構成されているとのことです。

- 署名：PGCOPY\n\377\r\n\0という11バイトで構成されるとのこと ( 50 47 43 4f 50 59 0a ff 0d 0a 00 )
- フラグフィールド：32 ビットで構成されるとのこと。ただ、現在は 16 ビット箇所以外は 0 である必要がある。
- ヘッダ拡張領域長：こちらも 32 ビット。現在は全部 0 とのこと。

タプル

以下の順に続く感じのようです。

- フィールの数を表す 16 ビットの整数
- フィールドデータのバイト長を表す 32 ビットの整数
- 実際のデータ。

※ フィールドデータのバイト長には NULL フィールドを表す -1 も入るようで、この場合、実際のデータ部分はナシとのこと。

トレーラ

16 ビットでどちらも -1 とのことです。


ちょっと実際のファイルの内容を確認してみます。

1. テーブルの作成

今回は integer と bytea 型のフィールドを持つテーブルとします。

testdb=# create table test_table (id integer not null, file bytea, primary key (id));
CREATE TABLE
testdb=# insert into test_table values (1, E'\\xDEADBEEF');
INSERT 0 1
testdb=# select * from test_table where id = 1;
 id |    file    
----+------------
  1 | \xdeadbeef
(1 row)

2. copy を利用してバイナリ形式でファイルを出力

testdb=# copy (select * from test_table where id = 1) to '/tmp/postgres/copyoutput' (format binary);
COPY 1

※ ファイル出力用のディレクトリ

# mkdir /tmp/postgres/
# chown postgres:postgres /tmp/postgres/

3. 出力されたファイルの確認

# hexdump -C /tmp/postgres/copyoutput 
00000000  50 47 43 4f 50 59 0a ff  0d 0a 00 00 00 00 00 00  |PGCOPY..........|
00000010  00 00 00 00 02 00 00 00  04 00 00 00 01 00 00 00  |................|
00000020  04 de ad be ef ff ff                              |.......|
00000027

各要素の対応は以下の感じになっています。

署名：50 47 43 4f 50 59 0a ff 0d 0a 00
フラグフィールド：00 00 00 00
ヘッダ拡張領域長：00 00 00 00
フィールの数：00 02
フィールドデータのバイト長：00 00 00 04 ( id )
実際のデータ：00 00 00 01
フィールドデータのバイト長：00 00 00 04 ( file )
実際のデータ：de ad be ef
トレーラ：ff ff


ちなみにフラグフィールドについては OID が含まれていると 16 ビット部分が 1 になるとのことです。

testdb=# create table test_table2 (name varchar(50)) with oids;
CREATE TABLE
testdb=# insert into test_table2 values ('hoge');
INSERT 16538 1
testdb=# select * from test_table2;
 name 
------
 hoge
(1 row)

testdb=# copy test_table2 to '/tmp/postgres/copyoutputoid' (format binary, oids on);
COPY 1

出力ファイルをみると 16 ビット部分が 1 になってますね。oid は 40 9a ( 16538 ) の部分。この場合、フィールドの箇所は 00 01 になるみたいです。

# hexdump -C /tmp/postgres/copyoutputoid 
00000000  50 47 43 4f 50 59 0a ff  0d 0a 00 00 01 00 00 00  |PGCOPY..........|
00000010  00 00 00 00 01 00 00 00  04 00 00 40 9a 00 00 00  |...........@....|
00000020  04 68 6f 67 65 ff ff                              |.hoge..|
00000027


以上になります。

[ 環境情報 ]
CentOS 7
PostgreSQL 9.5.2