报错invalid input syntax for type bytea的解决方案

0x00前言

从PostgreSQL 数据库中把数据导出来，并保存成csv格式的文件的时候报错。开始是运行没有问题，后来数据多了一些就报错了。一度怀疑人生。

0x01 运行环境

Cent OS 6.4 x64
psql (PostgreSQL) 9.4.15

0x02 问题

解决 ERROR: invalid input syntax for type bytea

报错大义是不可用的bytea类型输入语法。

select cast (request as bytea) from nginx_log where id = 5567;
ERROR:  invalid input syntax for type bytea

时间: 0.201s

这个报错很无语，因为没有指出哪里有问题。syntax一度让我认为是语法有问题，但是又不像以往的语法错误会指明错误位置。
看下bytea的官方定义【传送门】：

8.4.1. bytea Hex Format
The “hex” format encodes binary data as 2 hexadecimal digits per byte, most significant nibble first. The entire string is preceded by the sequence \x (to distinguish it from the escape format). In some contexts, the initial backslash may need to be escaped by doubling it (see Section 4.1.2.1). For input, the hexadecimal digits can be either upper or lower case, and whitespace is permitted between digit pairs (but not within a digit pair nor in the starting \x sequence). The hex format is compatible with a wide range of external applications and protocols, and it tends to be faster to convert than the escape format, so its use is preferred.
Example:
SELECT ‘\xDEADBEEF’;

bytea是二进制数据的16进制表现格式，这中格式的字符串以’\x’为前缀（以区分其转义格式）。在一些特殊的上下文中，原始的反斜杠可能需要通过翻倍来转义。日，发现google翻译比我翻译的好，还是用谷歌的吧。

bytea格式
"hex"格式将二进制数据编码为每字节2个十六进制数字，最高有效位在前。 整个字符串以序列\ x开头（以将其与转义格式区分开）。 在某些情况下，可能需要通过将其加倍来转义初始反斜杠（请参见第4.1.2.1节）。对于输入，十六进制数字可以是大写或小写，并且在数字对之间允许空格（但在数字对之内或在开始的\ x序列中均不允许）。十六进制格式与广泛的外部应用程序和协议兼容，并且其转换速度通常比转义格式更快，因此首选使用。

也就是说如果原始数据中有反斜杆\就需要转义变成\\才行。看下我的数据

select request from nginx_log where id = 5567;
|request|
|/news.php?pid=..\..\..\..\..\..\..\..\windows\win.ini|

你妹的果然有问题

增加对反斜杠的转移，搞定

select cast (replace(request, '\', '\\') as bytea) from nginx_log where id = 5567;

0x03 问题小结

request字段是varchar类型的，转换成bytea可能会出现’'需要转义的问题

0x04 参考文献

http://www.postgresql.org/docs/9.5/interactive/datatype-binary.html