[GIS] How to change character set in all columns of many tables in Postgresql / PostGIS

encodingpostgispostgresqlsql

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 ?

Best Answer

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.

Related Question