无法将PostgreSQL文本列转换为bytea

Question

在我的应用程序中，我使用一个带有“文本”列的postgresql数据库表来存储腌制的python对象。作为数据库驱动程序，我使用psycopg2，并且直到现在，我只向DB传递Python字符串（而不是Unicode对象）并从DB检索字符串。这基本上运行良好，直到最近我决定以更好/正确的方式处理字符串，并将以下结构添加到我的DB层：

psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)

在我的应用程序中，这基本上在任何地方都能很好地工作，并且我现在尽可能使用unicode对象。但是对于包含腌制对象的文本列的这种特殊情况，它会出现问题。我在我的测试系统中以这种方式使其正常工作：

• 检索数据： SELECT data :: bytea、params FROM mytable
• 编写数据： execute（“UPDATE mytable SET data =％s”，（psycopg2.Binary（cPickle.dumps（x）），））

但不幸的是，在生产系统中，我对某些列进行SELECT时会出现错误：

psycopg2.DataError: invalid input syntax for type bytea

当我尝试在psql shell中运行查询时，也会出现这个错误。

基本上，我计划将该列从"text"转换为"bytea"，但上述错误也阻止了我执行此转换。

据我所见，（在将该列作为纯Python字符串检索时），字符串中只有ord(c)<=127的字符。

- powo

Answer 1

问题在于将text强制转换为bytea并不意味着将字符串中的字节组装成一个bytea值，而是将该字符串解释为bytea类型的转义输入值。这样做行不通，主要是因为pickle数据包含大量反斜杠，而bytea会特别解释它们。

请尝试使用以下方法代替：

SELECT convert_to(data, 'LATIN1') ...

将字符串转换为LATIN1编码的字节序列（bytea值）。对于您来说，确切的编码并不重要，因为它都是ASCII字符（但没有ASCII编码）。

- Peter Eisentraut

---

你是完全正确的...我在此期间进行了进一步的分析，也发现只有带反斜杠的字符串受到了影响（不幸的是，在我的测试数据中没有这些字符串）。 我设法用 replace(column, '\', '\\') 转换了数据，但你的方法看起来更优雅。 - powo

+1. 这个嵌套的函数调用对我有效：encode(convert_to(concat(api_key.key,concat(':', api_key.shared_secret)),'LATIN1'),'base64') - emery