PostgreSQL中copy命令以二进制形式传输数据的二进制格式

有用户想知道pg中copy命令使用二进制文件时二进制文件的格式，这个格式pg的使用文档里其实已经有了，这儿结合例子说明的更具体一些，现解析如下：

先看一下copy命令的语法，然后做个例子，根据例子解析一下二进制文件的格式。

一

COPY命令用于在外部文件（客户端）和表之间传送数据，语法如下：

COPY tablename [ ( column [, ...] ) ]

    FROM { 'filename' | STDIN }

    [ [ WITH ]

          [ BINARY ]

          [ OIDS ]

          [ DELIMITER [ AS ] 'delimiter' ]

          [ NULL [ AS ] 'null string' ]

          [ CSV [ HEADER ]

                [ QUOTE [ AS ] 'quote' ]

                [ ESCAPE [ AS ] 'escape' ]

                [ FORCE NOT NULL column [, ...] ]

 

COPY { tablename [ ( column [, ...] ) ] | ( query ) }

    TO { 'filename' | STDOUT }

    [ [ WITH ]

          [ BINARY ]

          [ OIDS ]

          [ DELIMITER [ AS ] 'delimiter' ]

          [ NULL [ AS ] 'null string' ]

          [ CSV [ HEADER ]

                [ QUOTE [ AS ] 'quote' ]

                [ ESCAPE [ AS ] 'escape' ]

                [ FORCE QUOTE column [, ...] ]

 

下面是pg7.3版本以前的语法，后续的版本仍然支持

COPY [ BINARY ] table_name [ WITH OIDS ]

    FROM { 'filename' | STDIN }

    [ [USING] DELIMITERS 'delimiter' ]

    [ WITH NULL AS 'null string' ]

 

COPY [ BINARY ] table_name [ WITH OIDS ]

    TO { 'filename' | STDOUT }

    [ [USING] DELIMITERS 'delimiter' ]

    [ WITH NULL AS 'null string' ]

   

二

使用copy命令的例子：

1

命令copy to 涉及的表copybinarytest的定义

beigang=# \d+ copybinarytest

                        资料表 "enterprisedb.copybinarytest"

  栏位   |         型别          | 修饰词 |   存储   | Stats target | 描述

---------+-----------------------+--------+----------+--------------+------

 id      | numeric(10,0)         |        | main     |              |

 content | character varying(30) |        | extended |              |

有 OIDs: 否

 

2

表copybinarytest的记录

beigang=# select * from copybinarytest;

 id |   content

----+-------------

  1 | beigang

  2 | copy binary

  3 | abc

(3 行记录)

 

3

创建命令copy from 涉及的表copybinarytest2

beigang=# create table copybinarytest2 as select * from copybinarytest where 1=8;

SELECT 0

 

4

命令copy from 涉及的表copybinarytest2的定义

beigang=# \d+ copybinarytest2;

                        资料表 "enterprisedb.copybinarytest2"

  栏位   |         型别          | 修饰词 |   存储   | Stats target | 描述

---------+-----------------------+--------+----------+--------------+------

 id      | numeric(10,0)         |        | main     |              |

 content | character varying(30) |        | extended |              |

有 OIDs: 否

 

5

用copy to命令以二进制形式把copybinarytest表的内容拷贝到binary文件中

beigang=# copy binary copybinarytest to 'binary';

COPY 3

beigang=#

 

6

Binary文件的内容如下图：

 

 

 

Copy命令二进制文件的格式如下：

二进制格式

    二进制格式的文件由一个文件头、数据（零个或多个元组）和文件尾构成。文件头和数据按网络字节顺序表示。

 

文件头

    文件头由15个字节的固定区域和一个变长的扩展区域组成。固定区域包括：

       签名

              11个字节的序列PGCOPY\nFF\r\n\0。

       标志位

              32位的整数。位的编号从0（最低位）到31（最高位）。当前只有一个标志位被使用，它是第16位，如果它的值是1，表示含有OID，如果它的值是0，表示不含OID。其它的标志位永远是0。

       扩展区域

              首先是一个32位的整数，表示扩展区域的长度，不包括这个32位的整数自身。当前，它的值是0。第一个元组紧跟在它的后面。

 

元组

    每个元组以一个16位的整数开始，表示元组中域的个数。然后是元组中的每个域。每个域由一个32位的整数开始，它表示域的长度，后面跟着域的数据，-1表示域的值为空值。如果文件中包含OID，则它的值跟在元组的第一个16位整数的后面，而且就算组的域的个数时，OID不被计算在内，它也由一个32位的整数开始，表示OID的长度，当前，OID的长度固定是4字节，以后可能扩展到8字节。

文件尾

    文件尾包含一个16位的整数，它的值是-1。

7

确认copybinarytest2表中没有记录

beigang=# select * from copybinarytest2;

 id | content

----+---------

(0 行记录)

 

8

用copy from命令把binary文件中的数据拷贝到表从opybinarytest中

beigang=# copy binary copybinarytest2 from 'binary';

COPY 3

 

9

确认表copybinarytest2的内容和copybinarytest相同。

beigang=# select * from copybinarytest2;

 id |   content

----+-------------

  1 | beigang

  2 | copy binary

  3 | abc

(3 行记录)

 

三

以上面的例子为例解析Copy命令二进制文件格式

 

上图是copy命令导出的二进制数据文件，图中用红色竖线做分隔符，把二进制文件分成不同的段。蓝色数字表示各段的编号，

其中文件头包括第1段到第3段。

       第1段是11个字节的序列PGCOPY\nFF\r\n\0；第2段是标志位，占4个字节；第3段是扩展区域，至少4个字节，根据情况可能更多。

元祖包括第4段到第18段

              第4、5、6、7、8段是第一条记录：第4段表示该元祖的列数，占2个字节；第5段表示第一列的长度，占4个字节；第6段是第一列的值；第7段是第二列的长度，占4个字节；第8段是第二列的值。

              第9、10、11、12、13段是第二条记录，同上。

              第14、15、16、17、18段是第三条记录，同上。

文件尾是第19段，占2个字节。

 

参考：

http://www.postgresql.org/docs/9.2/static/sql-copy.html

src/backend/commands/copy.c

 

 

-----------------

转载请著明出处：
blog.csdn.net/beiigang
beigang.iteye.com