Doppelten Eintrag entfernen in einer Tabelle ohne Primary Key

[Mephisto]

Hallo, erstmalig hier im Forum,

weil ich gerade erst begonnen habe, mich mit Datenbanken auseinanderzusetzen, sind meine Erfolge noch mässig, aber durchaus ausbaufähig. Heute habe ich eine in MariaDB auf meinem Rechner eine ca. 1GB grosse TXT-Datei (internationale Stadtetabelle mit Geo-Daten von geonames.org) importiert.

Da es aber einen doppelten Eintrag gibt, ging nach dem manuellen Anlegen der Spalten der Import aber zunächst nicht, weil die erste Spalte als Primary-Key definiert wurde, was ich dann geändert habe, wonach der Import dann auch funktionierte (bis auf die Tatsache, dass die Connection irgendwann am Ende unterbrochen wurde !?).

Jetzt habe ich eine gigantische Tabelle und bei genauem Hinsehen ist mir auch klar geworden, warum das Ding so groß ist (30 Sekunden für eine Suche nach einem Ortsnamen !): Es gibt für viele Ortschaften mehrere Einträge aus unterschiedlichen Quellen mit leicht unterschiedlichen Daten. Dazu werde ich aber nochmal ein 2. Thema aufmachen, wenn ich damit nicht zurecht komme.

Das Hauptproblem ist aber, dass, wenn ich nach dem angemeckerten doppelten "Primary-Key" suche, ich tatsächlich zwei Einträge finde:

SELECT
    *
FROM
    all_cities
where
    `1` = '3039154'
limit 0 , 1000000;

Ergebnis:

'3039154', 'El Tarter', 'El Tarter', 'Ehl Tarter,Эл Тартер', '42.57952', '1.65362', 'P', 'PPL', 'AD', '', '02', '', '', '', '1052', '', '1721', 'Europe/Andorra'
'3039154', 'El Tarter', 'El Tarter', 'Ehl Tarter,Эл Тартер', '42.57952', '1.65362', 'P', 'PPL', 'AD', '', '02', '', '', '', '1052', '', '1721', 'Europe/Andorra'

Wie kann eine dieser Zeilen entfernen ?

Grüße
Mephisto

 

[Hony%]

Wie kann eine dieser Zeilen entfernen ?

Unter der Annahme, dass Datensätze mit identischem Primärschlüssel auch identische Daten enthalten wäre das eine schnelle Lösung:

INSERT INTO [TempTable]
SELECT DISTINCT *
FROM [Table]

Gruß
Hony

 

[Mephisto]

Ich nehme mal an, dass ich "[TempTable]" erst erstellen muss ? Eine Tabelle ohne Inhalt zu kopieren, habe ich schon versucht, aber nicht hinbekommen . Ist aber wahrscheinlich ganz einfach... Mit den Tuts habe ich so meine Probleme, weil SQL in meiner MySQL-Workbench irgendwie nicht so geschrieben wird, wie in den Beispielen, z.B. die Quotes bei der Bezeichnung der Tabellen-Spalte (s.o).

 

[akretschmer]

Ich nehme mal an, dass ich "[TempTable]" erst erstellen muss ? Eine Tabelle ohne Inhalt zu kopieren, habe ich schon versucht, aber nicht hinbekommen . Ist aber wahrscheinlich ganz einfach...

create table ... as ...

Wirklich einfach.

Mit den Tuts habe ich so meine Probleme, weil SQL in meiner MySQL-Workbench irgendwie nicht so geschrieben wird, wie in den Beispielen, z.B. die Quotes bei der Bezeichnung der Tabellen-Spalte (s.o).

MySQL ist auch eher NICHT als Lernsystem geeignet. Auch eher nicht als Produktivsystem. Höchstens als schlechtes Beispiel.

 

[Hony%]

Ich nehme mal an, dass ich "[TempTable]" erst erstellen muss ?

Die temporäre Tabelle soll die gleiche Definition wie die eigentliche Tabelle haben. Daher kannst du die einfach mit dem gleichen CREATE TABLE Statement unter einem anderen Namen anlegen.

Wenn du SQL lernen willst würde ich dir zu SQLite oder PostgreSQL raten. MySQL verhält sich in vielen Situationen einfach nur seltsam. Die Quotes sind nur ein Beispiel für die Sonderrolle.

 

[Mephisto]

Das ist ja jetzt eine wichtige Info für mich, weil ich ein grösseres Projekt angefangen habe, wobei dieses Mal auch Datenbanken zum Einsatz kommen sollen. Es geht um ein Programm (nach Java und C# jetzt in Python und PyQt), welches plattformübergreifend offline mit einem aktuellen Abbild einer Online-DB in einer SQLite arbeiten können soll.

Da ich im Non-Profit-Bereich arbeite, brauche ich natürlich eine kostenfreie DB-Lösung und im Internet und in Foren schwört man häufig auf MySQL/MariaDB. Wie gesagt, im Datenbank-Thema bin ich gerade erst angekommen. Aber wenn ich nun von Euch höre, dass sich MySQL in mancher Hinsicht "seltsam" verhält, werde ich skeptisch, ob ich da jetzt die richtige Wahl getroffen habe, erst recht, wenn ich etwas von "seltsam" höre. Davon gibt es bei Software eigentlich genug. Da hat man die Nerven sowieso schon oft genug blank.

Letztes Jahr hatte ich schon mal was kleineres (eine Tabelle) mit einer MSSQL-DB mit .NET/C# (und den Express-Programmen) gemacht. Da brauchte ich eigentlich fast kein SQL. Da hatte ich aber immer wieder Probleme mit irgendwelchen Permissions und der Lizenzierung auf unterschiedlichen Rechnern oder nach Neuinstallationen. Mittlerweile arbeite ich auch schon seit vielen Monaten nur noch unter Linux (Manjaro und Arch), wo ich den MS-SQL-Server sowieso nicht installiert bekomme und da war die MariaDB am naheliegendsten.

Aber PostGreSQL gibt es wohl auch unter Linux, ich habe aber keine Ahnung, ob ich dafür einen so umfangreichen Support vom Internet bekomme. Da müsste ich mich erst mal internetinformieren. PostGreSQL wurde mir bislang von keiner Seite empfohlen, geschweige denn, dass jemand etwas gegen MySQL gesagt hätte.

 

[akretschmer]

Aber PostGreSQL gibt es wohl auch unter Linux, ich habe aber keine Ahnung, ob ich dafür einen so umfangreichen Support vom Internet bekomme. Da müsste ich mich erst mal internetinformieren. PostGreSQL wurde mir bislang von keiner Seite empfohlen, geschweige denn, dass jemand etwas gegen MySQL gesagt hätte.

PostgreSQL entstand als "Forschungsobjekt" vor 25 oder so Jahren, BSD (Berkeley). Also, die Geschichte ist lang und kompliziert. Erst gab es da eine extra Abfragesprache, Michael Stonebraker hat die ersten Dinge dazu gemacht, das ist ein Urgestein der DB-Welt. Wenn Dich das interessiert: Google weiß da vieles drüber. Jedenfalls: das lief schon immer und am Anfang nur unter UNIX. Dann gab es eine Zeit, da konnte man es unter Kleinweich laufen lassen, aber in einem *NIX-Interpreter, Cygwin. Seit Version 8.0 (??) läuft es native AUCH unter Windows. Wie gesagt, unter *NIX schon immer. Lizenz: BSD. Also das Beste, was man für *kein* Geld bekommen kann.

Forschungsobjekt: von Anfang an war eines der wichtigsten Ziele: es sollte robust sein, ACID und so. Speed war, am Anfang, Nebensache. Von daher kommt auch das häufige Vorurteil, das es langsam sei. Das mag bis zu Versionen um 7.x / 8.x auch eine zum Teil berechtigte Kritik sein. Heute nicht mehr.

Support: ich zitiere hier mal Peter van Hardenberg von Heroku, dem mit einigen zehn- oder hundertausend Installationen größten Anwender von PG:

"I can answer that definitively. We chose Postgres because it was operationally more reliable than MySQL and at the time the founders of company believed in the portability of SQL databases.

As the years wore on, we discovered that basically Postgres was this diamond in the rough which had a set of features and a development community which was one of the most unrecognizedly awesome open source projects we'd ever seen, and had been quietly building an incredibly awesome tool without making a big fuss."

Quelle: http://www.quora.com/Heroku/What-were-the-reasons-for-Heroku-choosing-PostgreSQL-over-MySQL

Also: wenn Du Support von PG suchst, den gibt es für lau, aber erstklassig in zig Mailinglisten, Foren und Chats und für viel Geld auch durch Firmen wie 2nd Quadrant, Cybertec, EnterpriseDB oder Credativ. Und vielen anderen. Da brauchst keine Angst zu haben, ich kann Dir gerne ein Dutzend Support-Firmen nennen.

PostgreSQL hat nur ein Problem: keine Lobby, die da groß die Trommel rührt. Hat aber nix mit der Qualität des Systems zu tun.

Um ein Beispiel zu nennen: unser größer Kunde, der PostgreSQL einsetzt, hat in einer (logischen) Tabelle mehrere Milliarden Datensätze. Milliarden, nicht Millionen. Läuft völlig problemfrei UND SCNELL. Ja, man muß natürlich wissen, was man tut, also die Tabelle ist partitioniert und Abfragen nutzen EXCLUSION CONSTRAINTS, ohne denen wären da Abfragezeiten im Millisekundenbereich nicht möglich. Aber das sind Features, die halt da sind und funktionieren. Und das absolut robust.

 

[Mephisto]

Aber nochmal zu meinem Problem in diesem Thema: Die doppelte Zeile in der import.txt (1019MB groß) wird wohl auch bei der PostgreSQL-DB Stress machen (habe sie gerade installiert...) !
Mit der Methode, keinen Primarschlüssel vorzugeben, wird es aber wohl auch funktionieren. Ich weiß aber auch noch gar nicht, ob man mit der PostgreSQL-DB überhaupt eine TAB-getrennte TXT-Datei importieren kann.

 

[akretschmer]

Aber nochmal zu meinem Problem in diesem Thema: Die doppelte Zeile in der import.txt (1019MB groß) wird wohl auch bei der PostgreSQL-DB Stress machen (habe sie gerade installiert...) !

Wenn Du keinen Primary Key oder Unique hast: ja. Warum auch nicht. In PG gibt es noch versteckte Felder, aber das erkläre ich Dir mal in einer anderen Maus. Lektion für heute: jede Tabelle muß einen Primary Key haben.

Ich weiß aber auch noch gar nicht, ob man mit der PostgreSQL-DB überhaupt eine TAB-getrennte TXT-Datei importieren kann.

Das geht sehr gut mit COPY. Ein normales Dump nutzt auch nichts anderes, im Prinzip. Nur einen anderen Separator. Aber das ist nebensächlich.

test=*# copy tab_demo from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1  1  2  3
>> 2  2  3  4
>> 3  3  4  5
>> 4  4  5  6
>> \.
Time: 17860,724 ms
test=*# select * from tab_demo ;
 id | c1 | c2 | c3
----+----+----+----
  1 |  1 |  2 |  3
  2 |  2 |  3 |  4
  3 |  3 |  4 |  5
  4 |  4 |  5 |  6
(4 rows)

Spalten durch TAB getrennt beim COPY. Du kannst da auch was rein - PIPE - en.

 

[Mephisto]

Lizenz: BSD. Also das Beste, was man für *kein* Geld bekommen kann.

Cool !

von Anfang an war eines der wichtigsten Ziele: es sollte robust sein,

Robustheit ist auch das, was mir an einem guten Linux so gut gefällt und was mir sehr wichtig ist.

...wenn Du Support von PG suchst, den gibt es für lau, aber erstklassig in zig Mailinglisten, Foren und Chats und für viel Geld auch durch Firmen

Für Geld kann ich nicht. Habe auch nicht vor, mit der Sache Geld zu verdienen, jedenfalls nicht unmittelbar. Ich habe ein kleines Forschungsprojekt, bei der ich viel Mathematik und jetzt auch ne Datenbank brauche. Aber Support brauche ich in Form von Beispielen und Erklärungen, weil ich Autodidakt bin.

PostgreSQL hat nur ein Problem: keine Lobby, die da groß die Trommel rührt. Hat aber nix mit der Qualität des Systems zu tun.

Ist wie z.B. bei Linux ! Keine Lobby, und, das kann ich nach Jahrzehnten mit Windows im IT-Bereich ganz gut beurteilen, es ist trotzdem besser als die Lauten und Großen.

Da bin ich ja jetzt mal gespannt, was PG mit meiner riesigen Datei macht, die MariaDB fing ja zum Schluss an zu straucheln...

 

[Mephisto]

Lektion für heute: jede Tabelle muß einen Primary Key haben.

Diese Lektion hatte ich schon . Das ist mir schon klar. Aber anders liest das Ding es doch nicht ein.

Das geht sehr gut mit COPY. ...Spalten durch TAB getrennt beim COPY. Du kannst da auch was rein - PIPE - en.

Copy in der shell ? oder wo bist Du da ? Im Shellscripten bin ich nicht so gut, schreib mir lieber ein kleines Programm . PG kann also keine TAB-getrennten Files einlesen, wenn ich das richtig verstehe ?

 

[Hony%]

PG kann also keine TAB-getrennten Files einlesen, wenn ich das richtig verstehe ?

Dass genau das geht hat @akretschmer doch oben demonstriert.

Aber Support brauche ich in Form von Beispielen und Erklärungen, weil ich Autodidakt bin.

Grundsätzlich ist für's erste jedes SQL-Tutorial nutzbar. Wenn du Probleme hast kannst du gerne hier fragen. Hilfreich ist auch wenn du die Relationale Algebra verstehst. Auch wenn nicht zwingend erforderlich hilft es ungemein zu verstehen was da unter der Haube passiert.

 

[Mephisto]

Ok ! Da ist sowieso erst mal wieder rtfm angesagt. Aber jetzt bekomme ich erst mal das Ding nicht installiert ! Die Anleitung in der Arch-Wiki funktioniert so nicht: (Als postgres-user ausgeführt)

postgres -c "initdb --locale en_US.UTF-8 -E UTF8 -D '/var/lib/postgres/data'"

Da fehlt irgendwas, angeblich.
Das muss ja erst mal laufen ! Aber ist hier Off-Topic... Also erst mal forschen ...

 

[Mephisto]

Läuft jetzt ! Aber beim Import meiner Datei meldet phpPgAdmin "Kein Server angegeben!"
localhost ist in /etc/webapps/phppgadmin/config.inc.php eingetragen.
User, DB, Tabelle und Tabellenspalten sind eingerichtet.
Ich weiss, ist hier auch Off-Topic . Aber vielleicht hat ja jemand eine Idee.

 

[Hony%]

Läuft jetzt ! Aber beim Import meiner Datei meldet phpPgAdmin "Kein Server angegeben!"

Vielleicht hilft ja das hier: http://nullpointr.wordpress.com/2011/08/04/fehler-kein-server-angegeben-phppgadmin/