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.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", 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$
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