0

I have csv-file that is UTF-8 encoded. I'm using python (psycopg2) to copy it into a Postgres database. My Postgres database is using UTF-8. However I have problems to show Ä, Ö and Å characters.

If I'm using query SET client_encoding = 'ISO-8859-1'; It would fix the issue.

My database is looking like this (psql -l):

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
 data      | postgres | UTF8     | fi_FI.UTF-8 | fi_FI.UTF-8 |

My server locale is fi_FI.utf8

My CSV file is UTF-8. Its made with python command:

with open('users.csv', "w+", encoding="UTF-8", newline='') as f:
     writer = csv.writer(f)
     writer.writerows(list_table)

I'm writing it to Postgres table with this code:

cur = conn.cursor()

cur.execute("CREATE SCHEMA IF NOT EXISTS data;")

cur.execute("DROP TABLE IF EXISTS data.users"`)

cur.execute("CREATE TABLE data.users(name VARCHAR(40), lastname VARCHAR(40), phone VARCHAR(13), email VARCHAR(100), iban VARCHAR(18), id VARCHAR(11))")
copy_ = """
           COPY data.users FROM stdin WITH CSV HEADER`
           DELIMITER as ',' ENCODING 'UTF-8'
           """
with open('users.csv', 'r') as f:
    cur.copy_expert(sql=copy_sql, file=f)
    conn.commit()
    cur.close()

First I used this code

with open('users.csv', 'r') as f:
    next(f)
    cur.copy_from(f, 'data.users', sep=',')
    cur.close()

I switched to Copy_expert to get that ENCODING 'UTF-8' into the string. However it didn't do anything.

Now when I use pgadmin4 or console (Linux ubuntu 18.04LTS terminal) I can't get ÄÖÅ characters to show up. Mökkisuo is "Mökkisuo" and Lammasjärvi is "Lammasjärvi". I'm using basic query like select * from data.users to check results.

However if I change my client encoding to ISO-8859-1 everything shows like it should? (SET client_encoding = 'ISO-8859-1';).

Start of EDIT

Here is my locales.

~$ locale
LANG=fi_FI.utf8
LANGUAGE=
LC_CTYPE="fi_FI.utf8"
LC_NUMERIC="fi_FI.utf8"
LC_TIME="fi_FI.utf8"
LC_COLLATE="fi_FI.utf8"
LC_MONETARY="fi_FI.utf8"
LC_MESSAGES="fi_FI.utf8"
LC_PAPER="fi_FI.utf8"
LC_NAME="fi_FI.utf8"
LC_ADDRESS="fi_FI.utf8"
LC_TELEPHONE="fi_FI.utf8"
LC_MEASUREMENT="fi_FI.utf8"
LC_IDENTIFICATION="fi_FI.utf8"
LC_ALL=

~$ locale charmap
UTF-8

And here is how I get bad result!

:~$ sudo su - postgres
postgres@Server:~$ psql
postgres=# \c data
data=# select * from data.users;

result is like this:

 Piritta      | Mökkisuo        | 0    | piritta.mokkisuo@notreal.com

Oh and data is just generated. It's not real people data.

What I have to change to get this show correctly in UTF-8? I don't understand where that conversion to ISO-8859-1 is coming from?

I don't understand why file that is UTF-8, Database that is UTF-8. Locale that is UTF-8 etc. Has to be changed to Latin-1 (client encoding) to show up correctly? What is causing this? Everything should already be UTF-8? So why ISO-8859-1? Why UTF-8 doesn't show these characters?

  • Do you do SET client_encoding = 'ISO-8859-1'; when loading the data, or when viewing the data? – jjanes Nov 15 '19 at 15:29
  • When you say "console", what do you mean? xterm? – jjanes Nov 15 '19 at 15:32
  • I'm using Linux ubuntu LST 18.04 server. Console is terminal. so sudo su - postgress and psql I'm using first select * from data.users then I see that data doesn't show correctly and I changed to SET client_encoding = 'ISO-8859-1' After that I run select * from data.users again and it will show those äöå characters. If I change it back to UTF-8 I lost those characters again. – Bitterbit Nov 15 '19 at 16:05
  • It seems like your terminal is not prepared to deal with utf8. For me, it works correctly out of the box, and then stops working when I do set client_encoding = 'ISO-8859-1';. In other words, you have to make client_encoding match the actual client encoding. Your actual client encoding seems to be ISO-8859-1. – jjanes Nov 15 '19 at 16:19
  • Gnome terminal has an encoding option that may differ from the locale. Check Character encoding and the preferences, and the output of locale in the shell. – Daniel Vérité Nov 15 '19 at 19:05
1

I solved my own problem. Everything was right from server side and problem was few little things. My first mistake was that I made two text files that I used in my code to generate users. These two text files was in ANSI encoded. I made them with notepad in windows and didn't think much of it.

Even though Visual Studio Code show these ANSI encoded text-files correctly and writes them inside of UTF-8 csv-file correctly. Even if I open that csv-file with notepad++ it's show's those characters as UTF-8 encoded. However when I pushed them to database they all broke up. Not sure why they show up correctly in UTF-8 file in notepad++? Is there some weird translation correction?

I solved this to convert those ANSI text files to UTF-8 with notepad++.

However my second mistake was that I didn't open those files as UTF-8! So even when I converted them to UTF-8 my Visual Studio tried to read them as latin-1 (ISO-8859-1). This round they where broken before I pushed them to database. I took me while to notice, because end result was still the same as ANSI coded files.

I corrected this with adding encoding="utf-8" to all my open statements. Example here with open('users.csv', 'r',encoding="utf-8") as f: I wanted to be sure that Visual Studio Code will open them as UTF-8 and not some weird format.

This finally solved my issue!! This was really specific problem, so I'm not sure if this will help anyone else. However I wanted to post solution here just in case. I hate those post that just writes up that this is now solved and never tells how.

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.