在PostgreSQL 8.4中，如何将bytea转换为文本值？

Question

在我的应用程序中，我使用C代码将数据插入数据库，因为我从不可信的来源接收到的字符串，我使用libpq库的PQescapeByteaConn函数对它们进行了转义。这个方法运行得非常好，即结果是八进制格式的字符串。请看下面的例子， 输入字符串：\n\t\f\b\p\k\j\l\mestPrepared 输出字符串：\\012\\011\\014\\010pkjlmestPrepared 输出字符串被插入到数据库中。现在，我在Java代码中使用JDBC从数据库中检索这些数据。我如何将字符串还原回其原始值？ 我考虑了两种可能的方法， 1. 更改数据库检索查询，并将该字段传递给Postgres的任何字符串操作函数，即可以将bytea转换为文本。 2. 在Java代码中进行解码。 我可以理解方法1会更有效率。我已经尝试了几乎所有列在here的函数，但没有任何效果。请帮忙！ 我在Linux机器上使用的是Postgres的8.4版本。

- Amit

---

JDBC驱动程序应该能够解码bytea。ResultSet.getBytes()有什么问题吗？ - Daniel Vérité

@DanielVérité 我会尝试并告诉你结果。 - Amit

Answer 1

对于关注者来说，由于这似乎是“将bytea转换为文本”的规范问题（即，您可以在pgAdmin等工具中实际看到它）。以下是如何使其可见（假设your_column_name是类型bytea）： select encode(table.your_column_name, 'escape') as your_alias_name from table_name

- rogerdpack

---

怎么才能将其反转？只需使用decode(..., 'escape')吗？ - Koen

看起来是的：https://stackoverflow.com/a/49926257/32453 - rogerdpack

Answer 2

你尝试过使用encode(data bytea, format text)函数并选择escape格式吗？在这个语法中，format可以是以下任意一种：

• base64
• hex
• escape

所以，encode(E'123\\000456'::bytea, 'hex')会将bytea以十六进制编码输出。

- valgog

---

这应该是对问题的评论，除非你确定它解决了问题。如果确实解决了问题，你可以详细说明为什么以及如何解决，以便于未来的读者受益。 - Hannah Vernon

@Valgog 我已经尝试过了。。 - Amit

Answer 3

将BYTEA转换为TEXT需要您了解文本的内部编码。如果不知道编码，您无法做任何事情。在普通的text列中，数据库会根据SERVER_ENCODING设置存储文本。例如，在您的示例中，\n被转换为\012。这是编码的属性，对于整个宇宙来说并非客观真实。 如果您知道该编码，那就很简单了。

1. 我们将您的输入字符串视为编码的文字。
2. 我们对其进行编码。这会生成一个转义字符串（类型为text）。
3. 然后我们必须解码它以获取bytea类型。
4. 现在拥有bytea类型并不允许我们返回到text。我们必须告诉它bytea所使用的文本格式，使用convert_from函数。我们告诉它使用UTF-8编码。

以下是一个示例。

SELECT convert_from(decode(x, 'escape'), 'UTF-8')
FROM encode(E'\n\t\f\b\p\k\j\l\mestPrepared'::bytea, 'escape')
  AS t(x);

- Evan Carroll