Moving PostgreSQL database fails on non-ascii characters with 'value too long'

Question

# 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.

Answer 1

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 (\l of psql utility helps). If they differ, recreate destination database with -Exxx option.