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 ?
-
3Character set is a property of a database. I think you need to reload all the data. – Vince May 7 '17 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. – Gpetr May 7 '17 at 17:01
-
1If 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 '17 at 20:00
This won't solve any of your problems and will be an epic cluster mess-up. The "database" has three types of encoding,
- Internal encoding.
- Import encoding.
- 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.
-
1
-
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 ? – Gpetr May 8 '17 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 '17 at 16:35
