Moving a PostgreSQL database fails on non-ascii characters with a "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 with an error for the whole table.

psql: /home/me/myDatabase.tar: 660266: ERROR: value too long for a character of type (100) CONTEXT: COPY myTable, line 591, column myColumn: "Former member of the State Department" Project for the Future of Iraq "and now in the Atlantic compartment ... "

These hats are annoying curly single and double quotes. It seems to me that they first fit into the column, but somewhere in the export / import process they expand, and then no longer fit into the column with the changing symbol (100).

I actually move the database on the server, for which I have little rights, so the sql solution will be great. Is there a way to do something like

UPDATE myTable SET myColumn = removeNonAscii(myColumn) WHERE hasNonAscii(myColumn)

EDIT: Habe got this. I changed

createdb -h myServer -U myUser -T template0 myDatabaseCopy

to

createdb -h myServer -U myUser -T template0 -E UTF8 myDatabaseCopy

and it did the trick.

Answer 1

It seems that the problem is caused by the encoding of the database. For example, the source database is encoded in "UTF-8", and the destination has a more limited character set, such as "SQL_ASCII".

Check the encodings of both databases (utility \lfrom psql). If they are different, recreate the destination database with the option -Exxx.