postgresql图片等二进制数据的存储(copy命令，bytea类型)

COPY语句

COPY table_name [ ( column_name [, ...] ) ]    FROM { 'filename' | STDIN }    [ [ WITH ] ( option [, ...] ) ]COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }    TO { 'filename' | STDOUT }    [ [ WITH ] ( option [, ...] ) ]where option can be one of:    FORMAT format_name    OIDS [ boolean ]    DELIMITER 'delimiter_character'    NULL 'null_string'    HEADER [ boolean ]    QUOTE 'quote_character'    ESCAPE 'escape_character'    FORCE_QUOTE { ( column_name [, ...] ) | * }    FORCE_NOT_NULL ( column_name [, ...] )    ENCODING 'encoding_name'

FORMAT：输入/输出格式(text, csv, binary)，默认的是text。

DELIMITER：输入/输出文本的分割符，文本文件默认的是tab，csv默认的逗号，此选项对二进制文件无效。另：分隔符只能为单字符。

NULL：代表空值的字符。

HEADER：是否包含第一行，只对csv文件有效。

copy导入的时候默认将整个文件的内容copy到整个表里，除此之外，可以指定导入的表的字段，导出的时候除了支持(所有字段/部分字段)导出外，还支持自定义查询语句导出。因为整个的导入导出操作是在一个事务里完成的，所以速度比insert要快。

bytea类型

bytea是二进制字符串，类似于varchar/text，但其存储单位不是字符而是字节，它可以用来存储一个字节序列，其中可以包含0字节(zero octet)以及其他一些不可打印(non-printable，值在[32, 126]范围之外的)字符。在用户看来这些就是原始的字节(raw bytes)，不像varchar/tex，bytea没有什么字面意义。在postgresql中还有OID类型，用来专门操作大对象，二者的对比如下表所示：

Characteristic

BYTEA

OID

Max. allowed space

1 GB

2 GB

Data access

As a whole

Stream-style

Storage

In defined table

In pg_largeobject system table

Data manipulation

Using SQL and escaping sequnces

Only within transaction block by special functions

Loading

Preload

On demand

图片文件的存取

如图片等文件，不能直接read之后存，因为不是postgresql支持的标准二进制格式，所以不支持。如果要存的话，需要转一步：

xxd  -p /home/user/myimage.png | tr -d '\n' > /tmp/image.hexecho "-- CREATE TABLE hexdump (hex text);DELETE FROM hexdump;COPY hexdump FROM '/tmp/image.hex';-- CREATE TABLE bindump (binarydump bytea);DELETE FROM bindump;INSERT INTO bindump (binarydump)  (SELECT decode(hex, 'hex') FROM hexdump limit 1);" | psql mydatabase

其中，xxd的作用就是将一个文件以十六进制的形式显示出来。图片文件可以以二进制的形式存储，但这些二进制的字节数据不能写到文件里，需要用能识别这些字节数据的工具。比如在python中可以这样：

from PIL import Imageimport psycopg2 import StringIOif __name__ == "__main__":        conn = psycopg2.connect(host="127.0.0.1", user="postgres", password="", database="postgres")    cur = conn.cursor()        query_sql = "select binarydump from bindump limit 1"    cur.execute(query_sql)        rcd = cur.fetchone()        img_data = rcd[0]        img = Image.open(StringIO.StringIO(img_data))        img.save("1.png","PNG")

在SQL语句中，可以用decode函数，将普通的字节码数据转为标准的bytea类型，在python中，可以借助psycopg2.Binary(buffer)。

import psycopg2 if __name__ == "__main__":        conn = psycopg2.connect(host="127.0.0.1", user="postgres", password="", database="postgres")    cur = conn.cursor()        img_buffer = None    with open("1.png") as reader:        img_buffer = reader.read()         insert_sql = "insert into bindump(binarydump) values(%s)"    params = (psycopg2.Binary(img_buffer),)         cur.execute(insert_sql, params)         conn.commit()

再来个sql函数，如果当前表里已经有了该图片就更新，没有就插入：

CREATE OR REPLACE FUNCTION update_img(img_id character varying,img_name character varying,img_data bytea)RETURNS void AS$BODY$declareimg_exists boolean;beginselect exists(select 1 from my_img where imgid=img_id) into img_exists;if(img_exists) thenupdate my_imgset imgname = img_name,    imgdata = img_datawhere imgid = img_id;elseinsert into my_img values(img_id, img_name, img_data);end if;end;$BODY$  LANGUAGE plpgsql VOLATILE  COST 100;ALTER FUNCTION update_img(character varying, character varying, bytea)  OWNER TO postgres;

参考：

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

http://initd.org/psycopg/docs/module.html

http://dba.stackexchange.com/questions/1742/how-to-insert-file-data-into-a-postgresql-bytea-column

https://www.microolap.com/products/connectivity/postgresdac/help/tipsandtricks_byteavsoid.htm