如何向postgreSQL中添加bytea类型的大对象数据

如果想要在PostgreSQL中存储二进制数据，例如存储Word、Excel文档，图片文件等，可以使用bytea类型的列。bytea类型是PostgreSQL特有的存储二进制数据的字段类型，与SQL标准中的BLOB和BINARY LARGE OBJECT类型异曲同工。这在PostgreSQL文档的bytea类型介绍中有所说明。

接下来先说说如何向表中插入、更新bytea数据。

PostgreSQL允许在SQL命令中包含bytea类型的数据，以便能够使用INSERT向表中插入含有二进制数据的记录，使用UPDATE和调用与bytea类型相关的函数更新和操作bytea类型数据。二进制数据是一个字节序列，然而SQL命令是文本字符串，怎样在SQL中写入二进制数据呢？答案很简单，把每一个字节转换成对应的三位十进制数字的八进制数字符串表示，以双斜线做为前缀，即0x00表示为00、0x2C表示为2C、0xFF表示为377，并按照bytea类型的要求在字符串前端的单引号外注明E。举例如下：

INSERT INTOtable1 (fileid,filename,content)VALUES(1,'filename.doc',E'000102');
INSERT INTOtable1 (fileid,content)VALUES(2,'anotherfile.jpg',E'00377');
UPDATEtable1SETcontent = E'000000'WHEREfileid = 1;
UPDATEtable1SETcontent = content || E'377377377'WHEREfileid = 2;

可以在INSERT INTO中包含整个文件的bytea类型字符串，也可以像上面第四行那样，分块追加。对于短小的二进制数据，在命令控制台中编辑SQL命令也未尝不可。但是如果要存储一个图片文件或者Word文档之类的大型二进制数据的时候，就需要借助数据访问借口，或者自己写一个字节转换程序，直接操作SQL语句。

插入bytea数据后可以使用SELECT语句获取它。如下所示：

SELECTcontentFROMtable1;

在命令控制台中，我们会看到以输入时的字符串格式输出二进制数据，这是PostgreSQL做的转换。在Python中使用psycopg2模块，执行上述SELECT语句后能够获得原始的二进制字节字符串，可以直接写入二进制文件。

顺便说明一下。对于字节的转换，PostgreSQL的文档说的非常详细，按照零字节、单引号、斜线，以及字符的可打印性分别作了讨论。原因是需要逃逸单引号和斜线字符，另外可打印字符可以不作转换，直接出现。

1. 比如有一个实体定义例如：
/**
*@generated
*@display数据
*/
@Column(name = "f_data")
private byte[] data;
/**
*@display数据
*/
@Column(name = "f_data2")
private Integer[] data2;

要保存到postgres过程中会出现data读取数据混乱，不是原来的数据，data2会出现无法反序列化问题

解决办法：修改postgres的postgresql.conf配置文件
bytea_output = 'escape'# hex,escape
意思是设置bytea_output的输出类型设置为转义类型输出，而postgres默认是hex类型输出，所以导致转换数据混乱问题

参考文档：
8.4. Binary Data Types
The bytea data type allows storage of binary strings; see Table 8-6.
...

18.10. Client Connection Defaults
18.10.1. Statement Behavior
bytea_output (enum)
Sets the output format for values of type bytea. Valid values are hex (the default) and escape (the traditional PostgreSQL format). See Section 8.4 for more information. The bytea type always accepts both formats on input,regardless of this setting.

以上配置需要重启postgres服务才生效！！！

参考资料

PostgreSQL Document:

http://www.postgresql.org/docs/9.2/static/datatype-binary.html

PostgreSQL public API(LargeObject):http://jdbc.postgresql.org/documentation/publicapi/index.html

PostgreSQL JDBC Interface:

http://jdbc.postgresql.org/documentation/head/binary-data.html

二进制类型bytea的操作（在最大值内，有内存限制）

①直接插入逃逸序列

bytea 文本逃逸八进制

http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html#DATATYPE-BINARY-TABLE