Menu

PostgreSQL ALTER TABLE: Spalten hinzufügen und ändern

ALTER TABLE ändert eine bestehende Tabelle: Spalten hinzufügen, umbenennen und löschen, den Typ einer Spalte mit USING ändern, Defaults und NOT NULL setzen und Constraints anlegen.

Diese Seite enthält ausführbare Editoren. Bearbeiten, ausführen und Ausgabe sofort sehen.

ALTER TABLE ändert eine Tabelle, die es schon gibt. Am häufigsten brauchst du ADD COLUMN, RENAME COLUMN ... TO, DROP COLUMN und ALTER COLUMN ... TYPE. Mehrere Aktionen passen in eine Anweisung, getrennt durch Kommas.

Die bestehenden Zeilen bekommen 'free' in plan und NULL in signed_up. Seit PostgreSQL 11 schreibt das Hinzufügen einer Spalte mit konstantem Default die Tabelle nicht neu, das geht also auch bei großen Tabellen schnell. Ein volatiler Default wie clock_timestamp() schreibt dagegen weiterhin jede Zeile neu.

Spalte oder Tabelle umbenennen

Umbenennen geht sofort und behält Daten, Indizes und Constraints. Views funktionieren weiter, weil sie auf die interne ID der Tabelle verweisen und nicht auf ihren Namen. Namen, die anderswo als Text stehen, ziehen nicht mit: Funktionsrümpfe in PL/pgSQL und Abfragen in deiner Anwendung nutzen weiter den alten Namen. Such also danach, bevor du umbenennst.

Spalte löschen

DROP COLUMN entfernt die Spalte samt ihren Daten. Es schlägt fehl, wenn eine View oder ein Foreign Key von der Spalte abhängt. CASCADE löscht diese mit, und IF EXISTS überspringt den Fehler, wenn die Spalte schon weg ist.

PostgreSQL schreibt die Tabelle dabei nicht neu. Es markiert die Spalte als gelöscht, und der Platz wird frei, wenn Zeilen aktualisiert werden oder die Tabelle neu geschrieben wird.

Typ einer Spalte ändern

ALTER COLUMN ... TYPE wandelt jeden Wert um. Gibt es keinen automatischen Cast vom alten zum neuen Typ, ergänzt du USING mit einem Ausdruck, der den neuen Wert berechnet:

Ohne USING scheitert diese Anweisung mit column "price" cannot be cast automatically to type numeric. Lässt sich auch nur ein Wert nicht umwandeln, schlägt die ganze Anweisung fehl und die Tabelle bleibt unverändert.

Die meisten Typänderungen schreiben die Tabelle neu. Manche nicht: ein varchar(n) verlängern oder varchar in text ändern.

Default und NOT NULL setzen oder entfernen

SET NOT NULL durchsucht die Tabelle und schlägt fehl, wenn eine Zeile NULL enthält. Füll die Lücken also vorher:

DROP DEFAULT und DROP NOT NULL machen das rückgängig. Ein neuer Default gilt nur für künftige Inserts. Bestehende Zeilen behalten ihre Werte.

Constraint hinzufügen

ALTER TABLE orders ADD CONSTRAINT total_positive CHECK (total > 0);
ALTER TABLE orders DROP CONSTRAINT total_positive;

Ein neuer CHECK oder Foreign Key durchsucht zur Prüfung die ganze Tabelle. Bei einer großen, stark genutzten Tabelle legst du ihn zuerst mit NOT VALID an, dann werden nur neue Zeilen geprüft. Danach führst du ALTER TABLE orders VALIDATE CONSTRAINT total_positive; aus. Das prüft die alten Zeilen unter einem schwächeren Lock, bei dem Lesen und Schreiben weiterlaufen. Die Seite zu Foreign Keys nutzt dasselbe Muster, und die Referenz zu ALTER TABLE listet jede Form und ihren Lock auf.

Häufig gestellte Fragen

Kann ich die Reihenfolge der Spalten in PostgreSQL ändern?

Nein. ALTER TABLE kann keine Spalte verschieben, und neue Spalten landen immer am Ende. Gib die Spalten in deinem SELECT in der gewünschten Reihenfolge an, oder leg eine neue Tabelle in dieser Reihenfolge an und kopiere die Daten.

Sperrt ALTER TABLE die Tabelle in PostgreSQL?

Die meisten Formen nehmen einen ACCESS EXCLUSIVE Lock, der Lesen und Schreiben blockiert, bis die Anweisung fertig ist. Schnelle Änderungen (umbenennen, Spalte löschen, Spalte mit konstantem Default hinzufügen) halten ihn nur kurz. Eine Typänderung, die die Tabelle neu schreibt, hält ihn während des ganzen Umschreibens.

Wie füge ich in PostgreSQL mehrere Spalten auf einmal hinzu?

Trenne die Aktionen in einer Anweisung mit Kommas: ALTER TABLE users ADD COLUMN phone text, ADD COLUMN city text;. Alle Aktionen in einem ALTER TABLE laufen zusammen, mit einem einzigen Lock.

Lässt sich ALTER TABLE in PostgreSQL zurückrollen?

Ja. DDL ist in PostgreSQL transaktional, ein ALTER TABLE innerhalb von BEGIN ... ROLLBACK wird also komplett rückgängig gemacht.

Illustration der Programmiersprachen bei Coddy

Lerne mit Coddy zu programmieren

Los geht's