插入时出现Postgres错误-错误：编码“ UTF8”的字节序列无效：0x00 [英] Postgres error on insert - ERROR: invalid byte sequence for encoding "UTF8": 0x00

问题描述

将mysql中的数据插入到postgres中时，出现以下错误。

I get the following error when inserting data from mysql into postgres.

是否必须从输入数据中手动删除所有空字符？
有没有办法让postgres为我做这件事？

Do I have to manually remove all null characters from my input data? Is there a way to get postgres to do this for me?

ERROR: invalid byte sequence for encoding "UTF8": 0x00

推荐答案

PostgreSQL不支持存储NULL（文本字段中的 0x00字符（显然与完全支持的数据库NULL值不同）。

PostgreSQL doesn't support storing NULL (\0x00) characters in text fields (this is obviously different from the database NULL value, which is fully supported).

来源： http://www.postgresql.org/docs/9.1/static /sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE

如果需要存储NULL字符，则必须使用bytea字段-应该存储您想要的任何内容，但不支持文本操作。

If you need to store the NULL character, you must use a bytea field - which should store anything you want, but won't support text operations on it.

鉴于PostgreSQL不支持文本值，因此没有很好的方法获取将其删除。您可以将数据导入bytea，然后使用特殊功能（可能在perl或其他功能中）将其转换为文本，但是在加载之前进行预处理可能会更容易。

Given that PostgreSQL doesn't support it in text values, there's no good way to get it to remove it. You could import your data into bytea and later convert it to text using a special function (in perl or something, maybe?), but it's likely going to be easier to do that in preprocessing before you load it.

这篇关于插入时出现Postgres错误-错误：编码“ UTF8”的字节序列无效：0x00的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！