Dodawanie i usuwanie kolumn w tabeli

Wyobraź sobie, że projektujesz bazę danych dla uniwersytetu. Na początku tabela studentów ma tylko dwa pola: ID i imię. Proste, studenci zaczynają korzystać z systemu. Ale po miesiącu przychodzą do ciebie wykładowcy i mówią: "A można jeszcze dodać wiek?", a chwilę później: "Potrzebny jeszcze email". No i potem: "A może w końcu usuńmy pole middle_name, i tak nikt go nie wypełnia!". Właśnie wtedy przydadzą ci się skille z ALTER TABLE do dodawania i usuwania kolumn.

Składnia dodawania kolumn

No więc, żeby dodać nową kolumnę do istniejącej tabeli PostgreSQL, używasz komendy ALTER TABLE. Zobacz na składnię:

ALTER TABLE table_name
ADD COLUMN column_name data_type [constraints];

• table_name — nazwa tabeli, do której chcesz dodać nową kolumnę.
• column_name — nazwa kolumny, którą chcesz dodać.
• data_type — typ danych, np. VARCHAR, INTEGER albo DATE.
• constraints (opcjonalny parametr) — ograniczenia, które chcesz nałożyć na tę kolumnę, np. NOT NULL, DEFAULT itd.

Przykład: dodajemy nową kolumnę

Załóżmy, że mamy tabelę students, która została wcześniej utworzona:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Nagle pojawiło się zadanie, żeby dodać kolumnę age do przechowywania wieku studentów. Robisz to tak:

ALTER TABLE students
ADD COLUMN age INTEGER;

Teraz struktura tabeli students wygląda tak:

Jeśli będziesz dodawać nowe rekordy bez podania wieku, PostgreSQL automatycznie ustawi wartość NULL, bo nie daliśmy żadnych ograniczeń typu NOT NULL albo wartości domyślnej (DEFAULT).

Składnia usuwania kolumn

Usuwanie kolumny z tabeli też robisz przez ALTER TABLE. Oto składnia:

ALTER TABLE table_name
DROP COLUMN column_name [RESTRICT | CASCADE];

• table_name — nazwa tabeli, z której chcesz usunąć kolumnę.
• column_name — nazwa usuwanej kolumny.
• RESTRICT — zakaz usunięcia kolumny, jeśli są do niej odwołania (domyślnie).
• CASCADE — usunięcie kolumny razem ze wszystkimi zależnościami.

O kasadowym usuwaniu zależności opowiem więcej na kolejnym poziomie :P

Przykład: usuwamy niepotrzebną kolumnę

Wróćmy do tabeli students. Stwierdziliśmy, że pole age już nie jest potrzebne. Usuwamy je:

ALTER TABLE students
DROP COLUMN age;

Teraz struktura tabeli znowu jest uproszczona:

Przydatne przykłady

Dodawanie kolumny z ograniczeniami

A gdybyś jednak chciał dodać kolumnę age, ale z ograniczeniem NOT NULL i wartością domyślną? Wtedy użyj takiego kodu:

ALTER TABLE students
ADD COLUMN age INTEGER NOT NULL DEFAULT 18;

Teraz we wszystkich istniejących wierszach dla kolumny age ustawi się wartość 18, a nowe rekordy będą wymagały podania wartości dla tego pola, jeśli nie użyjesz wartości domyślnej.

Usuwanie kolumny z zależnościami

Jeśli jakaś kolumna ma zależności (np. jest używana w widokach albo triggerach), musisz użyć modyfikatora CASCADE. Ale uważaj: to może wpłynąć na inne obiekty w twojej bazie. Przykład:

ALTER TABLE students
DROP COLUMN email CASCADE;

Typowe błędy i niuanse

Błąd: dodanie kolumny z nieprawidłowym typem danych.
Wyobraź sobie, że dodajesz kolumnę typu INTEGER, ale próbujesz trzymać w niej tekst. PostgreSQL nie ogarnie i wywali błąd przy dodawaniu rekordów. Typ danych zawsze musi pasować do zawartości.

Błąd: usuwanie kolumny bez uwzględnienia zależności.
Na przykład, jeśli usuniesz kolumnę, do której są indeksy albo klucze obce, PostgreSQL może wywalić błąd, jeśli nie dasz modyfikatora CASCADE.

Błąd: usuwanie kolumny z zależnościami. Na przykład, jeśli usuniesz kolumnę, do której są indeksy albo klucze obce, PostgreSQL może wywalić błąd, jeśli nie dasz modyfikatora CASCADE. Dodałeś go i usunąłeś tysiące wierszy, które się odwoływały do twojej usuniętej kolumny. Których nie trzeba było usuwać : (

Niuans: kolejność wykonywania zmian.
Czasem dodawanie kolumn z ograniczeniami wymaga ogarnięcia kolejności operacji. Na przykład, jeśli dodajesz kolumnę z NOT NULL, to najpierw musisz ją wypełnić wartościami domyślnymi, bo inaczej PostgreSQL wywali błąd.