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

Postgre SQL 2020-06-21

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.hex
echo "
-- 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 Image
import psycopg2 
import StringIO

if __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;