ALTER TABLE zmienia tabelę, która już istnieje. Najczęściej potrzebujesz ADD COLUMN, RENAME COLUMN ... TO, DROP COLUMN i ALTER COLUMN ... TYPE. Kilka akcji można zmieścić w jednym poleceniu, rozdzielając je przecinkami.
Istniejące wiersze dostają 'free' w plan i NULL w signed_up. Od PostgreSQL 11 dodanie kolumny ze stałą wartością domyślną nie przepisuje tabeli, więc działa szybko nawet na dużych tabelach. Zmienna wartość domyślna, taka jak clock_timestamp(), nadal przepisuje każdy wiersz.
Zmiana nazwy kolumny lub tabeli
Zmiana nazwy jest natychmiastowa i zachowuje dane, indeksy oraz ograniczenia. Widoki dalej działają, bo odwołują się do wewnętrznego identyfikatora tabeli, a nie do jej nazwy. Nazwy zapisane gdzie indziej jako tekst nie nadążają: ciała funkcji PL/pgSQL i zapytania w aplikacji nadal używają starej nazwy, więc wyszukaj ją przed zmianą.
Usunięcie kolumny
DROP COLUMN usuwa kolumnę razem z danymi. Polecenie kończy się błędem, gdy od kolumny zależy widok albo klucz obcy. CASCADE usuwa również je, a IF EXISTS pomija błąd, gdy kolumny już nie ma.
PostgreSQL nie przepisuje tu tabeli. Oznacza kolumnę jako usuniętą, a miejsce wraca w miarę aktualizowania wierszy albo przy przepisaniu tabeli.
Zmiana typu kolumny
ALTER COLUMN ... TYPE konwertuje każdą wartość. Gdy nie ma automatycznego rzutowania ze starego typu na nowy, dodaj USING z wyrażeniem, które oblicza nową wartość:
Bez USING to polecenie kończy się błędem column "price" cannot be cast automatically to type numeric. Jeśli choć jednej wartości nie da się przekonwertować, całe polecenie się nie udaje, a tabela zostaje bez zmian.
Większość zmian typu przepisuje tabelę. Niektóre nie: wydłużenie varchar(n) albo zmiana varchar na text.
Ustawianie i usuwanie wartości domyślnej oraz NOT NULL
SET NOT NULL przegląda tabelę i kończy się błędem, jeśli któryś wiersz zawiera NULL, więc najpierw uzupełnij braki:
DROP DEFAULT i DROP NOT NULL cofają te ustawienia. Nowa wartość domyślna dotyczy tylko przyszłych wstawień. Istniejące wiersze zachowują swoje wartości.
Dodanie ograniczenia
ALTER TABLE orders ADD CONSTRAINT total_positive CHECK (total > 0);
ALTER TABLE orders DROP CONSTRAINT total_positive;
Dodanie CHECK albo klucza obcego przegląda całą tabelę, żeby ją sprawdzić. Przy dużej, mocno używanej tabeli dodaj je najpierw z NOT VALID, wtedy sprawdzane są tylko nowe wiersze. Potem uruchom ALTER TABLE orders VALIDATE CONSTRAINT total_positive;. Sprawdza ono stare wiersze pod słabszą blokadą, przy której odczyty i zapisy działają dalej. Strona o kluczach obcych używa tego samego wzorca, a dokumentacja ALTER TABLE wymienia każdą formę i jej blokadę.
Najczęściej zadawane pytania
Czy w PostgreSQL można zmienić kolejność kolumn?
Nie. ALTER TABLE nie przesuwa kolumn, a nowe kolumny zawsze trafiają na koniec. Wypisz kolumny w potrzebnej kolejności w SELECT albo utwórz nową tabelę w tej kolejności i skopiuj dane.
Czy ALTER TABLE blokuje tabelę w PostgreSQL?
Większość form zakłada blokadę ACCESS EXCLUSIVE, która wstrzymuje odczyty i zapisy do końca polecenia. Szybkie zmiany (zmiana nazwy, usunięcie kolumny, dodanie kolumny ze stałą wartością domyślną) trzymają ją tylko przez chwilę. Zmiana typu, która przepisuje tabelę, trzyma ją przez całe przepisywanie.
Jak dodać kilka kolumn naraz w PostgreSQL?
Rozdziel akcje przecinkami w jednym poleceniu: ALTER TABLE users ADD COLUMN phone text, ADD COLUMN city text;. Wszystkie akcje jednego ALTER TABLE wykonują się razem, z jedną blokadą.
Czy ALTER TABLE można wycofać w PostgreSQL?
Tak. DDL w PostgreSQL jest transakcyjny, więc ALTER TABLE wewnątrz BEGIN ... ROLLBACK zostaje w całości cofnięty.