I have a postgresql (version 8.1) database that has the encoding set to SQL_ASCII, combined with several apps that some are running using UTF8 and some with LATIN1. With this scope I have a database with the data divided in LATIN1 and UTF8.

I have the difficult task to deploy a brand new server, running PostgreSQL 9.3 with the database with the UTF8 encoding. But I'm stuck!

Here is what I have tried so far with no success:

  • pg_dump forcing UTF8: error converting non-utf8 to utf8
  • pg_dump forcing LATIN1: no error during dump or restore, but the characters with accentuation are totally wrong
  • pg_dump forcing LATIN1 and conversion with iconv to UTF8: same error as above

Any ideas?

  • 2
    Wow, that's not going to be fun at all. Are the utf-8 and latin-1 data divided cleanly into different tables / schemas? Or are they mixed together in all tables? – Craig Ringer May 15 '14 at 1:35
  • have you tried this approach agiletesting.blogspot.co.uk/2012/03/…? they used gnu.org/software/recode... – Leo May 15 '14 at 10:07
  • @CraigRinger it is all mixed together, unfortunately – vsmoraes May 15 '14 at 13:30
  • @Leo I've tried with iconv but I'll give recode a shot. – vsmoraes May 15 '14 at 13:32
  • If it's mixed together you're in a right mess, because pretty much all utf-8 is also valid (if usually weird) latin-1, and some latin-1 is valid utf-8. Personally I'd be doing it on a field-by-field basis with some Python or whatever, using some heuristics based on my knowledge of common characters etc to make a best-guess effort. – Craig Ringer May 15 '14 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.