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

概述COPY语句 COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] ( option [, ...] ) ]COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' 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",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$declare	img_exists boolean;begin	select exists(select 1 from my_img where imgID=img_ID) into img_exists;	if(img_exists) then		update my_img		set imgname = img_name,imgdata = img_data		where imgID = img_ID;	else		insert 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

总结

以上是内存溢出为你收集整理的postgresql图片等二进制数据的存储(copy命令，bytea类型)全部内容，希望文章能够帮你解决postgresql图片等二进制数据的存储(copy命令，bytea类型)所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。