6

I have inserted hundreds of tables (from shapefile format) in a Postgresql / Postgis database with the shp2pgsql tool. But because I have encoding issues, I want to change character set in all columns of every table from UTF-8 to ISO-8859-7. How is it possible ? Is it feasible with an ALTER TABLE sql statement in every table or it's a more complicated issue ?

Improve this question
3
  • 3
    Character set is a property of a database. I think you need to reload all the data.
    – Vince
    May 7, 2017 at 16:16
  • Thank you Vince for your comment. After reading some more, I think that I have to 1) dump my database, 2)rename or drop my existing database, 3)create new database with the suitable encoding, 4) reload my data.
    – Capdi
    May 7, 2017 at 17:01
  • 1
    If you've mangled the text due to character set issues, export/import will not make anything better; you really need to reload from source.
    – Vince
    May 7, 2017 at 20:00

1 Answer 1

Reset to default
5

This won't solve any of your problems and will be an epic cluster mess-up. The "database" has three types of encoding,

  1. Internal encoding.
  2. Import encoding.
  3. Client encoding.

The Internal Encoding doesn't matter at all. That's the encoding of the database. If the database only needs characters in ASCII, the internal encoding could just be ascii. That doesn't actually help your encoding issues though. Because UTF-8 supports everything useful and is pretty fast and space efficient, you can usually keep it and use UTF-8 (except for a few edge cases which almost certainly don't apply to you).

The Import Encoding, or psql's client_encoding or COPY's encoding option sets the encoding of the files you're importing. This must correct. If your files are ISO-8859-7 you need to tell that to COPY.

The client_encoding further sets the output encoding which has to be readable by the terminal. Every terminal has a desired type of character encoding, usually on modern systems that's UTF-8.

Improve this answer
3
  • 1
    An epic cluster mess-up, good phrase.
    – John Powell
    May 8, 2017 at 7:00
  • Thank you for your helpful answer. But could you be more specific ? For example in an existing database with many tables how can I set client_encoding with COPY command without inserting again the data ?
    – Capdi
    May 8, 2017 at 16:31
  • no, if data is in your database and it was imported in the wrong encoding then you have to reimport it or read it out and reencode it (which is likely more work and slower), but the database encoding does not have to change, just the Import encoding.
    – Evan Carroll
    May 8, 2017 at 16:35

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.