pixel
Seite wählen

Encoding “leicht gemacht” mit PostgreSQL

von | Dez 10, 2020 | PostgreSQL

In der Urzeit der Computerei passierte das alles in den USA. Und die Vereinigten Amerikaner haben seinerzeit wie üblich nicht über den Tellerrand geschaut, sondern ihren Zeichensatz an ihre Sprache angepasst (ASCII). Irgendwann “durften” dann auch die Alliierten ran, und die Probleme kamen auf… dieser Zeit verdanken wir ISO-8859-1, WIN-1252 und wie sie alle heißen. Mittlerweile gibt es aber ja seit 30 Jahren Unicode, die Probleme sind also doch Schnee von gestern. Richtig?

PostgreSQL ist – aus guten Gründen – ein beliebtes Ziel für Migrationen. Und PostgreSQL ist von vorne bis hinten auf UTF-8 aufgestellt. Leider hat man es aber oft mit Altsystemen, Altdaten und Gammelcode zu tun, die irgendwie übernommen werden müssen.

So bestehen unfassbare Mengen an altem Perl-, Python-, PHP-, VBA-, … Code, der für frühe Access-, MySQL-, MS-SQL-, Oracle-, Sybase oder was auch immer für Datenbanken geschrieben wurde und mit Unicode nichts am Hut hat. (Einige dieser Systeme sind bis heute nicht wirklich gut darin…)

Gerne sind wir auch gezwungen, “Pseudo-CSV” aus M$ Excel zu importieren (warum ist es “normal”, comma separated values mit Semikolon zu separieren?!?), und Microsoft tut sich bis heute immer noch schwer mit UTF-8.


~$ file importtest.win-1252.csv
importtest.win-1252.csv: ISO-8859 text, with CRLF line terminators

Betreiber solcher Software haben sich üblicherweise aus der Affäre gezogen, indem sie die Datenbank, auch bei Updates, immer wieder im alten, überholten Encoding betrieben haben. Jetzt steht also die Umstellung auf PostgreSQL an, und beim ersten Testimport von Altdaten kommt diese Meldung:


blog=# \COPY csvimport FROM importtest.win-1252.csv WITH (DELIMITER ';', FORMAT CSV);
ERROR: invalid byte sequence for encoding "UTF8": 0xfc
CONTEXT: COPY csvimport, line 1
blog=#

Viele Entwickler*innen werden jetzt reflexhaft ihre bewährte Methode benutzen wollen, und ja, ich kann, auch wenn mein DB-Cluster anders initialisiert wurde, genau das auch tun:


blog=# \h CREATE DATABASE
Command: CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LOCALE [=] locale ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ] ]

siehe auch hier.

Ah, da ist es ja, [ ENCODING [=] encoding ] als “Weg des geringsten Widerstands”. Dann ist das doch normal, oder nicht?! Ja, kann man so machen, ist dann nur kacke und man hat eine Top-Gelegenheit verpasst! Besser ist es, die Gelegenheit zu nutzen und hier und jetzt den Weg auf UTF-8 einzuleiten. Dafür reicht es, das client_encoding für den Import anzupassen:


blog=# SHOW client_encoding ;
client_encoding
-----------------
UTF8
(1 row)
blog=# SET client_encoding TO 'win-1252';
blog=# \COPY csvimport FROM importtest.win-1252.csv WITH (DELIMITER ';', FORMAT CSV);
blog=#

Gut! Das hat schonmal geklappt. Dann schauen wir doch mal, wie das in der Tabelle aussieht:


blog=# SELECT * FROM csvimport ;
id | spalte1 | spalte2
----+-----------------------------+----------------------------
1 | Dies ist eine ▒bliche Datei | [NULL]
2 | mit M▒ll von M$ Excel | [NULL]
3 | [NULL] | und leeren Spalten (w▒rg).
(3 rows)

Pfui bah! Genau das wollten wir doch vermeiden!!!

Alles ist gut, unser client_encoding ist ja noch kaputt:


blog=# RESET client_encoding;
blog=# SELECT * FROM csvimport ;
id | spalte1 | spalte2
----+-----------------------------+----------------------------
1 | Dies ist eine übliche Datei | [NULL]
2 | mit Müll von M$ Excel | [NULL]
3 | [NULL] | und leeren Spalten (würg).
(3 rows)

Und wenn das Refakturieren aller beteiligten Komponenten gerade nicht opportun ist, kann das client_encoding z.B. per Rolle (User) gesetzt werden:


blog=# CREATE ROLE legacy_import LOGIN;
blog=# ALTER ROLE legacy_import SET client_encoding TO 'win-1252';

Alles, was von dieser Rolle nun geschrieben wird, verarbeitet PostgreSQL nun intern zu UTF-8. Alles, was die Rolle liest, wird in WIN-1252 umgewandelt. Alle modernen Clients können aber mit Unicode arbeiten, so dass einer Transition statt einer reinen Migration jetzt “nur noch” Code im Wege steht, keine Daten mehr.

P.S.: Es gibt natürlich noch weitere Möglichkeiten, das Encoding pro Client festzulegen, z.B. die Umgebungsvariable PGCLIENTENCODING.

 

Über den Autor:

Gunnar “Nick” Bluth hat seine Liebe zu relationalen Datenbanken Ende des letzten Jahrtausends entdeckt. Über MS Access und MySQL 3.x landete er sehr schnell bei PostgreSQL und hat nie zurückgeschaut, zumindest nie ohne Schmerzen. Er verdient seine Brötchen seit beinahe 20 Jahren mit FOSS (Administration, Schulungen, Linux, PostgreSQL). Gelegentlich taucht er auch tiefer in die Programmierung ein, so als SQL-Programmierer bei der Commerzbank oder in App-Nebenprojekten.
Lennart Betz
Lennart Betz
Senior Consultant

Der diplomierte Mathematiker arbeitet bei NETWAYS im Bereich Consulting und bereichert seine Kunden mit seinem Wissen zu Icinga, Nagios und anderen Open Source Administrationstools. Im Büro erleuchtet Lennart seine Kollegen mit fundierten geschichtlichen Vorträgen die seinesgleichen suchen.
Mehr Beiträge zum Thema PostgreSQL

PostgreSQL Einstieg unter openSUSE-Leap 15.2

da die meisten User sich zwar mit MySQL oder MariaDB auskennen, werde ich heute mal auf PostgreSQL eingehen, wie man dieses Paket installiert und ein paar Kommandos zur Verwaltung einer Datenbank. PostgeSQL ist von den Kommandos ähnlich wie zum Beispiel MariaDB. Sie...

Schneller LIKEn

Nein, hier soll es nicht um Twitter, Instagram oder Youtube gehen, sondern um Datenbankabfragen in PostgreSQL wie diese: blog # SELECT * FROM kunden WHERE vorname LIKE 'Ann%'; Diese Abfragen sind recht häufig anzutreffen, man denke z.B. an Drop-Down-Boxen, die z.B....

Milano – PostgreSQL Conference Europe 2019

It has been a while since one of us visited a PostgreSQL Conference. So when the opportunity arose Lennart and I took it. Additionally, it is somewhat of a special pgconf this year, because the conference is held where it originated, Italy. With over 500 attendees the...