Fremdschlüssel auf varchar-PK-Spalte lässt sich nicht korrekt abfragen

Ich habe eine Tabelle mit Berufen, in denen die Bezeichnung PK ist. Die sieht so aus:

CREATE TABLE “Berufe”
(
“Ausbildungsberuf” character varying(255) NOT NULL,
“BKZ” integer,
“BKZb” character varying(5),
CONSTRAINT “Berufe_pkey” PRIMARY KEY (“Ausbildungsberuf”)
)
WITH OIDS;

Das DB-Encoding steht auf LATIN9. Nun habe ich das Phänomen, dass der Eintrag in einer anderen Tabelle, die einen Fremdschlüssel auf der Spalte Ausbildungsberuf hat, fehlschlägt. Bei der Analyse kann ich nicht einmal auf der Tabelle Berufe mit der Texteingabe des Berufs die Abfrage starten. Konkret heißt das, ich habe einen Eintrag ‘Maler & Lackierer/in’ und die Abfrage

SELECT * FROM “Berufe” WHERE “Ausbildungsberuf”=‘Maler & Lackierer/in’

liefert im PgAdmin kein Ergebnis. Nun hat der PgAdmin als Client-Encoding UTF-8, daher habe ich einmal mit convert getestet:

SELECT * FROM “Berufe” WHERE convert(“Ausbildungsberuf”, ‘LATIN9’, ‘UTF-8’)=‘Maler & Lackierer/in’

liefert eine Zeile. Andere Einträge, ebenfalls mit /, liefern in beiden Fällen eine Zeile. Es scheint also nicht an den “Sonderzeichen” zu liegen. Die Fremdschlüsselprüfung sollte auch kein Encoding-Problem haben, da wir da ja kein Client-Encoding setzen.

Hat jemand dazu eine Idee? Ich bin mit meinem Latin jedenfalls am Ende :frowning:

hmm … ich bin auch kein latino :smiley:

Warum in aller Welt mit OIDs? Welche PG-Version?
Warum nutzt Du nicht einfach das INT-Feld als PK?

Ein PK auf einem TEXT-Feld (oder char oder varchar) ist selten eine gute Idee.

Das DB-Encoding steht auf LATIN9. Nun habe ich das Phänomen, dass der Eintrag in einer anderen Tabelle, die einen Fremdschlüssel auf der Spalte Ausbildungsberuf hat, fehlschlägt. Bei der Analyse kann ich nicht einmal auf der Tabelle Berufe mit der Texteingabe des Berufs die Abfrage starten. Konkret heißt das, ich habe einen Eintrag ‘Maler & Lackierer/in’ und die Abfrage

SELECT * FROM “Berufe” WHERE “Ausbildungsberuf”=‘Maler & Lackierer/in’

liefert im PgAdmin kein Ergebnis. Nun hat der PgAdmin als Client-Encoding UTF-8, daher habe ich einmal mit convert getestet:

SELECT * FROM “Berufe” WHERE convert(“Ausbildungsberuf”, ‘LATIN9’, ‘UTF-8’)=‘Maler & Lackierer/in’

liefert eine Zeile. Andere Einträge, ebenfalls mit /, liefern in beiden Fällen eine Zeile. Es scheint also nicht an den “Sonderzeichen” zu liegen. Die Fremdschlüsselprüfung sollte auch kein Encoding-Problem haben, da wir da ja kein Client-Encoding setzen.

Hat jemand dazu eine Idee? Ich bin mit meinem Latin jedenfalls am Ende > :frowning: >

Was passiert in plain psql?

test=# CREATE TABLE "Berufe"
test-# (                    
test(# "Ausbildungsberuf" character varying(255) NOT NULL,
test(# "BKZ" integer,                                     
test(# "BKZb" character varying(5),                       
test(# CONSTRAINT "Berufe_pkey" PRIMARY KEY ("Ausbildungsberuf")
test(# );                                                       
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "Berufe_pkey" for table "Berufe"
CREATE TABLE                                                                                   
Zeit: 285,570 ms                                                                               
test=*# insert into "Berufe" values ('Maler & Lackierer/in',1,'abc');
INSERT 0 1                                                           
Zeit: 0,614 ms                                                       
test=*# commit;                                                      
COMMIT                                                               
Zeit: 0,525 ms                                                       
test=# SELECT * FROM "Berufe" WHERE "Ausbildungsberuf"='Maler & Lackierer/in';
   Ausbildungsberuf   | BKZ | BKZb                                            
----------------------+-----+------                                           
 Maler & Lackierer/in |   1 | abc                                             
(1 Zeile)                                                                     

Zeit: 28,883 ms
test=*# show client_encoding ;
 client_encoding              
-----------------             
 UTF8                         
(1 Zeile)                     

Zeit: 0,131 ms
test=*# set client_encoding = 'Latin9';                                                                                                                                   
SET                                                                                                                                                                       
Zeit: 33,147 ms                                                                                                                                                           
test=*# SELECT * FROM "Berufe" WHERE "Ausbildungsberuf"='Maler & Lackierer/in';                                                                                  
   Ausbildungsberuf   | BKZ | BKZb
----------------------+-----+------
 Maler & Lackierer/in |   1 | abc
(1 Zeile)

[/quote]


Andreas

weil PGAdmin dies standardmäßig so vorschlägt …

Oha, also einen großen Teil der Fragen kann ich mit “historisch gewachsen” beantworten. Die Originaldatenbanken ist eine MS Access-DB, der Konverter hat die Tabellen mit OIDs angelegt. Seinerzeit wurde das nicht hinterfragt. Ebenso ist es dem Primärschlüssel ergangen. In der anderen Tabelle, die den Fremdschlüssel hat, werden auch gelöschte Einträge angezeigt, dh der Fremdschlüssel ist ohne DELETE-Action. Um nun nicht diverse Logik für referenzierte, nicht mehr auswählbare Berufe zu implementieren, wurde einfach auf den Namen referenziert.

Ok, nun aber zu den anderen Ideen.
PG-Version ist 8.1.11, Maschine ist i386-pc-solaris2.10

Ausgaben der SQLs:

db1=# insert into "Berufe"("Ausbildungsberuf", "BKZ", "BKZb") values ('Maler & Lackierer/in Test',1,'abc');
INSERT 55906425 1
db1=# SELECT * FROM "Berufe" WHERE "Ausbildungsberuf"='Maler & Lackierer/in Test';
 lID |     Ausbildungsberuf      | BKZ | BKZb
-----+---------------------------+-----+------
 324 | Maler & Lackierer/in Test |   1 | abc
(1 row)
db1=# show client_encoding ;
 client_encoding
-----------------
 LATIN9
(1 row)

Direkt über ein und denselben Client funktioniert es also auch. Es kann sein, das zwischendurch verschiedene Clients mit unterschiedlichen Encodings Daten in die Tabelle gepumpt haben. Hat die DB diese vielleicht nicht korrekt konvertiert und unterschiedlich gespeichert? Wenn ja, wie bekomme ich die Daten bereinigt?

Danke, Tom

Möglicherweise, vielleicht auch haben manche Datensätze noch Leerzeichen hinten dran?

Andreas

Nach Leerzeichen und Zeilenumbrüchen habe ich geschaut - sind nicht vorhanden.
Ich versuche es heute abend einmal mit Dump & Restore. Vielleicht löst das das Problem.

Tom

Der Vollständigkeit halber: Dump&Restore hat tatsächlich geholfen.