02 May 2014
PostgreSQL的客户端工具psql对bytea和二进制文件的输入输出支持支持的并不好,假设用户在使用pg的bytea存放图片等,希望把图片下载下来存为文件,一般要使用编程api。如果非要使用psql客户端,其实也可以实现,只是比较麻烦。

 

【方式一】

使用pg_read_binary_file这个函数,可以直接将一个二进制文件读入,返回一个bytea类型。




pg_read_binary_file(filename text [, offset bigintlength bigint])

但是这种方式有一些限制,使它基本无法使用:

限制一:所读取的文件,只能是PG Server端PGDATA目录下的文件

限制二:这个函数需要superuser权限才能调用

 

【方式二】

将二进制临时使用base64转为ascii,然后进行相关的操作。如下演示:

psql读入一个二进制文件为bytea:

 

bytea_io_test的表定义如下:

\d bytea_io_test

Table "public.bytea_io_test"
Column  | Type  | Modifiers
---------+-------+-----------
content | bytea |

将客户端的一个二进制文件,使用psql插入到表中:

\set c `base64 xx.tgz`
insert into bytea_io_test select decode(:'c','base64');

将表中的一个bytea列,导出为本地一个文件:

\a
\t
select encode(content,'base64') as cc from bytea_io_test
\g | base64 -d > yy.tgz

【bytea格式的文本显示问题】

如果是把一个文本文件存储成为了bytea,用hex看不到文本的内容,而用escape看到的内容会把换行符等转成\xxx这种格式,使用下面的方法,可以将bytea显示成正常的文本格式(这里假定文本编码格式为UTF8,如果是其他编码格式,请替换第二个参数):

convert_from(bytea_colunm,'UTF8')