Bytea类型和空值，Postgres

Question

我在PostgreSQL中使用了bytea类型，据我理解，它只包含一系列字节。但是，我无法与空值很好地配合使用。例如：

=# select length(E'aa\x00aa'::bytea);
 length
--------
      2
(1 row)

我原本期望是5。此外：

=# select md5(E'aa\x00aa'::bytea);
               md5
----------------------------------
 4124bc0a9335c27f086f24ba207a4912
(1 row)

这是“aa”而不是“aa\x00aa”的MD5。显然，我做错了什么，但我不知道我做错了什么。由于某些原因，我使用的是较旧版本的Postgres（8.1.11）。（一回到家后，我会看看最新的Postgres是否有相同的问题...）

- Thanatos

---

8.4版本在我尝试构建字符串（在将其转换为bytea之前）时报告无效编码的错误。 - araqnid

Answer 1

试试这个：

# select length(E'aa\\000aa'::bytea);
 length
--------
      5

更新：为什么原始代码不起作用？首先，要理解一个斜杠和两个斜杠之间的区别：

pg=# select E'aa\055aa', length(E'aa\055aa') ;
 ?column? | length
----------+--------
 aa-aa    |      5
(1 row)

pg=# select E'aa\\055aa', length(E'aa\\055aa') ;
 ?column? | length
----------+--------
 aa\055aa |      8

在第一个例子中，我写的是一个字面字符串，其中有4个未转义的字符（'a'），和一个被转义的字符。斜杠在第一次解析时被消耗掉，将完整的\055 转换为单个字符（在本例中是“-”）。

在第二个例子中，第一个斜杠只是转义了第二个斜杠，一对\\被解析器转换为单个\，而055被视为三个字符。

现在，当将文本转换为bytea时，转义字符（在已经解析或生成的文本中）会再次被解析/解释！（是的，这很令人困惑）。

因此，当我写

时，它将被解释为结束段落标记，并且必须进行适当的转义才能正确地表示该字符。

 select E'aa\000aa'::bytea;

在第一次解析中，文字E'aa\000aa'被转换为一个内部文本，其中第三个位置有一个空字符（根据您的PostgreSQL版本，空字符被解释为EOS，文本被假定为长度为2；或在其他版本中会抛出非法字符串错误）。

相反，当我写

 select E'aa\\000aa'::bytea;

在第一次解析中，看到了字面字符串"aa\000aa"（八个字符），并分配给一个文本；然后在转换为bytea时，再次解析，字符序列'\000'被解释为null字节。

我认为postgresql在这方面有点糟糕。

- leonbloy

---

啊 - 我想我明白了：Postgres字符串无法表示二进制数据，因此它们使用转义形式，其中null是\000，我们必须键入'\000'。现在我看到了PQescapeBytea和PQunescapeBytea - 我假设如果我选择一个bytea列，我将得到这个中间的转义形式，然后必须通过PQunescapeBytea传递。现在这一切都更有意义了。 - Thanatos

是的，谢谢您的更新。这证实了我之前的想法，并且在您标记为“(Yes, this is confusing.)”的地方，我完全同意！ - Thanatos

有时候用base64输入来指定二进制字面量更简单易懂，例如，使用decode('AAAA','base64')而不是E'\\000\\000\\000'::bytea。9.0版本支持一种更简单的十六进制编码方式来处理bytea值，这使得代码更加清晰（即使用E'\\x000000'而无需使用繁琐的::bytea后缀）。 - araqnid

Answer 2

您可以使用普通字符串或美元引用字符串来代替转义字符串：

# select length('aa\000aa'::bytea);
 length 
════════
      5
(1 row)

# select length($$aa\000aa$$::bytea);
length 
════════
      5
(1 row)

我认为美元引用字符串是更好的选择，因为如果配置参数standard_conforming_strings关闭，则PostgreSQL将在常规和转义字符串常量中识别反斜杠转义。然而，从PostgreSQL 9.1开始，默认值为打开，这意味着反斜杠转义仅在转义字符串常量中被识别。

- Marcello Nuccio