# 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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With