Spalten löschen

Hallo,

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.

Danke für die Antwort.

MisterX

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)

Andreas

Danke für die Antwort.

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.

Wie würde hierfür eine Lösung aussehen?

Danke.

MisterX

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 …


Andreas

Hallo,

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’;

Was ist daran jetzt noch falsch?

MisterX

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



Andreas

Ein Schema, ja. Aber das information, welches diverse Tabellen und Views enthält.

Kann ich dieses Schema information dann auch benutzen, oder muss ich hier mein eigenes angeben aus dem ich die Spalten löschen möchte?

In der Doku findest Du array_accum() als userdefinierte Funktion, diese arbeitet exakt wie array_agg() in 8.4

Muss ich diese dann jedesmal davor laufen lassen, oder leg ich diese einmalig an?

MisterX

Das ist per SQL-Spec eine genormte Schnittstelle, um Informationen über z.B. Tabellen abzufragen.


Muss ich diese dann jedesmal davor laufen lassen, oder leg ich diese einmalig an?

MisterX

Du kannst diese Funktion einmalig erstellen und dann nutzen. So wie auch andere Objekte der Datenbank. Wie z.B. Tabellen.

Andreas

Guten Morgen,

Du kannst diese Funktion einmalig erstellen und dann nutzen. So wie auch andere Objekte der Datenbank. Wie z.B. Tabellen.

Ich habe diese User definierte Funktion nun einmalig laufen lassen. Wo finde ich diese dann wieder? Bei den Funktionen taucht sie nicht auf?

MisterX



retschmer@tux:~$ psql test                                                                                                                                                          
psql (8.4.1)                                                                                                                                                                         
Type "help" for help.                                                                                                                                                                

test=# create function demo_function() returns bool as $$begin raise notice 'es gibt mich noch'; return true; end; $$language plpgsql;
CREATE FUNCTION                                                                                                                       
test=*# commit;                                                                                                                       
COMMIT                                                                                                                                
test=# \q                                                                                                                             
kretschmer@tux:~$ psql test                                                                                                           
psql (8.4.1)                                                                                                                          
Type "help" for help.                                                                                                                 

test=# select * from demo_function();
NOTICE:  es gibt mich noch           
 demo_function                       
---------------
 t
(1 row)

test=*# \df+ demo_function
                                                                                    List of functions
 Schema |     Name      | Result data type | Argument data types |  Type  | Volatility |   Owner    | Language |                        Source code                         | Description
--------+---------------+------------------+---------------------+--------+------------+------------+----------+------------------------------------------------------------+-------------
 public | demo_function | boolean          |                     | normal | volatile   | kretschmer | plpgsql  | begin raise notice 'es gibt mich noch'; return true; end;  |
(1 row)

test=*# \x
Expanded display is on.
test=*# select * from pg_proc where proname = 'demo_function';
-[ RECORD 1 ]---+-----------------------------------------------------------
proname         | demo_function
pronamespace    | 2200
proowner        | 16385
prolang         | 16388
procost         | 100
prorows         | 0
provariadic     | 0
proisagg        | f
proiswindow     | f
prosecdef       | f
proisstrict     | f
proretset       | f
provolatile     | v
pronargs        | 0
pronargdefaults | 0
prorettype      | 16
proargtypes     |
proallargtypes  |
proargmodes     |
proargnames     |
proargdefaults  |
prosrc          | begin raise notice 'es gibt mich noch'; return true; end;
probin          |
proconfig       |
proacl          |

test=*#

Andreas

Ok, danke für die Demo.

Jetzt noch ne Frage?

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’;

Hat du mir auch hierfür ne Idee?

Danke.

MisterX

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.

Andreas

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.

Danke.

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…

Andreas

OK dann eben ein Aufruf.
So wie es gerade aussieht, brauch ich table_schema in der where.

Danke für Deine Antwort.