移动 PostgreSQL 数据库在非 ascii 字符上失败，并显示“值太长”；
# Dump my database to a tar file
pg_dump -f myDatabase.tar -F t -h myServer -U myUser -W -i myDatabase
# create a new database
createdb -h myServer -U myUser -T template0 myDatabaseCopy
# restore my database
pg_restore -d myDatabaseCopy -h myServer -U myUser myDatabase.tar
然后我收到此错误，整个表的导入失败。
psql:/home/me/myDatabase.tar:660266: 错误: 值对于类型字符变化来说太长 (100) 上下文：复制 myTable，第 591 行，myColumn 栏：“国务院‘伊拉克的未来’项目的前成员，现在在大西洋海岸......”
这些帽子是那些烦人的卷曲单引号和双引号。在我看来，它们一开始适合该列，但在导出/导入过程中的某个地方它们会扩展，然后不再适合字符变化（100）列。
实际上，我正在将实时数据库移动到我几乎没有权限的服务器上，因此仅 sql 的解决方案会很棒。有没有办法做类似
UPDATE myTable SET myColumn = removeNonAscii(myColumn) WHERE hasNonAscii(myColumn)
编辑的事情： 哈贝明白了。我改变
createdb -h myServer -U myUser -T template0 myDatabaseCopy
了
createdb -h myServer -U myUser -T template0 -E UTF8 myDatabaseCopy
，这就成功了。
# Dump my database to a tar file
pg_dump -f myDatabase.tar -F t -h myServer -U myUser -W -i myDatabase
# create a new database
createdb -h myServer -U myUser -T template0 myDatabaseCopy
# restore my database
pg_restore -d myDatabaseCopy -h myServer -U myUser myDatabase.tar
Then I get this error, and the import fails for an entire table.
psql:/home/me/myDatabase.tar:660266: ERROR: value too long for type character varying(100)
CONTEXT: COPY myTable, line 591, column myColumn: "A former member of the State Departmentâs âFuture of Iraqâ project and now at the Atlantic Cou..."
Those hat-a's are those annoying curly single and double quotes. It looks to me like they fit in the column at first, but somewhere in the export / import process they expand, and then no longer fit in the character varying(100) column.
I'm actually moving a live database on a server I have little permission for, so a sql only solution would be great. Is there a way to do something like
UPDATE myTable SET myColumn = removeNonAscii(myColumn) WHERE hasNonAscii(myColumn)
EDIT:
habe got it. I changed
createdb -h myServer -U myUser -T template0 myDatabaseCopy
to
createdb -h myServer -U myUser -T template0 -E UTF8 myDatabaseCopy
and that did the trick.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
看来问题是由数据库编码引起的。
例如，源数据库采用“UTF-8”编码，目标数据库采用更受限制的字符集（例如“SQL_ASCII”）。
检查两个数据库的编码（
psql实用程序的\l有所帮助）。如果它们不同，请使用
-Exxx选项重新创建目标数据库。It seems that the problem is caused by database encoding.
For example, source database is encoded in “UTF-8” and destination is in more restricted character set like “SQL_ASCII.”
Check the encodings of both databases (
\lofpsqlutility helps).If they differ, recreate destination database with
-Exxxoption.