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 (
Name | Owner | Encoding | Collate | Ctype | Access privileges data | postgres | UTF8 | fi_FI.UTF-8 | fi_FI.UTF-8 |
My server locale is
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 | firstname.lastname@example.org
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?