How to fix double-encoded UTF8 characters in postgres I have a dataset (shapefile) with the same problem as the post below:
https://stackoverflow.com/questions/11436594/how-to-fix-double-encoded-utf8-characters-in-an-utf-8-table
&quot;A previous LOAD DATA INFILE was run under the assumption that the CSV file is latin1-encoded. During this import the multibyte characters were interpreted as two single character and then encoded using utf-8 (again).
This double-encoding created anomalies like ÃƒÂ± instead of ñ.&quot;
However, the solution given is in mysql and not postgres, i tried it on postgres and it didn't work, just worked on mysql:
UPDATE tablename SET
    field = CONVERT(CAST(CONVERT(field USING latin1) AS BINARY) USING utf8);

I need to import and fix this shapefile using postgres because I will need to use postgis to do various spatial analyzes.
How can I solve this using postgis?
That is pretty similar in PostgreSQL:
convert_from(convert_to(textcol, 'LATIN1'), 'UTF8')