ich habe folgendes Problem.
Ich habe in meiner DB Tabellen, die zuviel Spalten besitzen. Jetzt weiß ich aber nur, welche Spalten ich behalten möchte, und nicht welche ich löschen will.
Also folgendermaßen:
Tabelle X hat die Spalten a b c d
und ich weiß, dass ich in Tabelle X nur die Spalte c behalten möchte.
Wie kann ich sowas technisch in SQl realisieren? Weil Bei Alter Table … Drop muss ich ja genau die Spalten angeben die ich löschen möchte.
Das ist simpel. Innerhalb von psql bekommst Du die Spalten mit \d angezeigt. Nun kennst Du die Tabelle und kannst die Spalten, die zu löschen sind, aufzählen.
Der aufwändigere Weg wäre, aus den Systemtabellen die Spalten zu erfragen und dann ein passendes Statement zu generieren.
test=# \d minor73
Table "public.minor73"
Column | Type | Modifiers
--------------+------------------------+------------------------------------------------------
id | integer | not null default nextval('minor73_id_seq'::regclass)
test | text |
beschreibung | text |
zeit | time without time zone |
test=# select 'alter table minor73 drop column ' || array_to_string(array_agg(column_name::text),', drop column ') || ';' from information_schema.columns where table_name='minor73' and column_name != 'id';
?column?
-----------------------------------------------------------------------------------
alter table minor73 drop column test, drop column beschreibung, drop column zeit;
(1 row)
-- Copy & Paste that ...
test=*# alter table minor73 drop column test, drop column beschreibung, drop column zeit;
ALTER TABLE
test=*# \d minor73
Table "public.minor73"
Column | Type | Modifiers
--------+---------+------------------------------------------------------
id | integer | not null default nextval('minor73_id_seq'::regclass)
Das Problem ist, dass ich dies für mehrere tausende von Tabellen machen muss, und ich dies nicht von Hand machen möchte, sonder automatisiert aus Java raus. Daher kann ich nur definieren, welche Spalte ich am Schluß noch haben möchte und nicht welche ich alle löschen will, weil das kann einmal nur eine sein und ein anderes mal aber auch vier. Und das weiß ich voraus nicht.
Eine Tabelle, die die Tabellen und zu behaltenden Spalten enthält. Eine Funktion, die daraus SQL generiert, welche wiederum wie von mir gezeigt via SQL aus information_schema.columns die zu droppenden Spalten pro Tabelle ermittelt und passende SQL erstellt, welche dann auszuführen sind. Das könnte z.B. dann via EXECUTE innerhalb der Funktion erfolgen.
Unterm Strch ist das nur eine Fleißarbeit basierend auf dem, was ich schon gezeigt habe. Pech für Dich, aß ich dazu jetzt zu faul bin …
ich habe versucht deine Funktion auszuprobieren. Nur ist mir nicht ganz klar ob du mit information_schema, dass Schema meinst.
Und dann bekomme ich folgende Fehlermeldung:
FEHLER: Funktion array_agg(text) existiert nicht
SQL Status:42883
Hinweis:Keine Funktion stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.
Zeichen:77
Mein Aufruf sieht wie folgt aus:
select ‘alter table v99.nb_gebiet drop column ’ || array_to_string(array_agg(column_name::text),’, drop column ') || ‘;’ from information_schema.columns where table_name=‘nb_gebiet’ and column_name != ‘gid’;
Ein Schema, ja. Aber das information, welches diverse Tabellen und Views enthält.
Und dann bekomme ich folgende Fehlermeldung:
FEHLER: Funktion array_agg(text) existiert nicht
SQL Status:42883
Hinweis:Keine Funktion stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.
Zeichen:77
Diese Funktion gint es erst am 8.4. Also hast Du eine ältere Version. In der Doku findest Du array_accum() als userdefinierte Funktion, diese arbeitet exakt wie array_agg() in 8.4
Wenn ich auf mehr als eine Spalte prüfen möchte die erhalten bleiben soll, hätte ich gedacht ich hänge sie mit AND z.B.: column_name != ‘the_geom’ hinten an. Dies funktioniert nur leider nicht.
select ‘alter table v99.nb_gebiet drop column ’ || array_to_string(array_agg(column_name::text),’, drop column ') || ‘;’ from information_schema.columns where table_name=‘nb_gebiet’ and column_name != ‘gid’;
Das funktioniert auch. Wenn man es richtig macht. Dazu gehört, daß man bei Tabellen, die nicht im search_path liegen, diesen mit nennt. Dies scheint mir die Ursache für deine fehlende Fehlermeldung zu sein.
Ich hab hierzu nochmal eine Frage, und zwar wenn ich folgende Funktion angebe:
array_to_string(array_accum(column_name::text),’, drop column ') || ‘;’ from information_schema.columns where table_name=‘nb_gebietsgrenze’ and column_name != ‘gid’ and column_name != ‘the_geom’;
Wie kann ich hier noch das Schema angeben? Da ich mehrere Schemen mit den gleichen Inhalten habe funktioniert das hier irgendwie glaub nicht ganz richtig.
Das ist keine Funktion, zumindest keine vollständige. information_schema.columns enthält eine Spalte table_schema, ob Du diese im WHERE nun brauchst oder im Select-Teil ist mir grad nicht ganz klar…