Zum Inhalt springen
aviral gupta
Kontakt aufnehmen

// B4.4 · ca. 30 Min. · Einstieg

ALTER TABLE und Standardwerte

Nach dieser Lektion ändern Sie eine Tabelle, die schon Daten enthält: neue Spalten und Namen, neue Standardwerte und Typen, neue Regeln und ids, die PostgreSQL selbst vergibt.

Lektion 4 von 5 in B4 Daten und Schema ändern

Danach können Sie

  • Spalten und Tabellen hinzufügen, löschen und umbenennen und sagen, was die vorhandenen Zeilen bekommen
  • Standardwerte, Typen und NOT NULL ändern und wissen, welche Zeilen jede Änderung prüft oder berührt
  • Neue Zeilen mit einer Identity-Spalte nummerieren, auch in einer Tabelle, die schon ids hat

  1. Aufwärmen · Aufgabe 1 von 7

    Aufwärmen aus der letzten Lektion. members.email ist UNIQUE, und Linus hat schon keine E-Mail. Was passiert, wenn ein fünftes Mitglied ohne E-Mail hinzukommt?

    INSERT INTO members VALUES (5, 'Alan', NULL, NULL, '2026-10-01');

  2. Vorhersagen · Aufgabe 2 von 7

    Sagen Sie es voraus, bevor Sie weiterlesen. members hat schon vier Zeilen. Die neue Spalte ist NOT NULL, also können die alten Zeilen nicht leer bleiben. Was passiert?

    ALTER TABLE members
  ADD COLUMN active boolean NOT NULL DEFAULT true;

  3. Üben · Aufgabe 3 von 7

    Ergänzen Sie das Schlüsselwort, damit die Spalte city von jetzt an town heißt und ihre Werte behält.

    ALTER TABLE members ____ COLUMN city TO town;
    ALTER TABLE members COLUMN city TO town;

  4. Üben · Aufgabe 4 von 7

    Ordnen Sie jedem ALTER TABLE members … zu, was es mit den vier vorhandenen Zeilen macht.

  5. Üben · Aufgabe 5 von 7

    Ein Import hat books.year als text-Spalte hinterlassen. Welche Anweisung macht daraus eine integer-Spalte, damit year + 1 wieder funktioniert?

  6. Denksport · Aufgabe 6 von 7

    Knobelaufgabe. books hat eine text-Spalte signed mit DEFAULT 'no' bekommen, und jetzt soll sie boolean werden. Der USING-Ausdruck passt für jede Zeile. Was passiert?

    ALTER TABLE books ADD COLUMN signed text DEFAULT 'no';

ALTER TABLE books
  ALTER COLUMN signed TYPE boolean USING signed = 'yes';

  7. Anwenden · Aufgabe 7 von 7

    Mini-Aufgabe. Geben Sie books im Editor des durchgerechneten Beispiels eine Spalte added_on: Die sechs Bücher, die schon da sind, bekommen 2026-09-01, später hinzugefügte Bücher 2026-10-01. Lassen Sie PostgreSQL neue Bücher nach den vorhandenen ids nummerieren. Fügen Sie dann Persuasion von Jane Austen mit 1 Exemplar hinzu, ohne id und ohne Datum, und zeigen Sie die Bücher 6 und 7.

    Prüfen Sie Ihr Ergebnis anhand dieser Liste

Selbst programmieren

Lesen Sie das ausgearbeitete Beispiel und lösen Sie dann die Übungen. Ihr Code läuft in Ihrem Browser oder auf Ihrem Computer und wird nie hochgeladen.

Ausgearbeitetes Beispiel

Die Bibliothek ändern, während sie Daten enthält

setup.sql baut die Bibliothek mit Schlüsseln und Regeln aus der letzten Lektion. main.sql fügt members eine Spalte status hinzu, ändert ihren Standardwert für neue Mitglieder, benennt city in town um und lässt PostgreSQL neue Ausleihen nach den vorhandenen ids nummerieren. Auf Ihrem Computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. A new column: existing rows get the default.
ALTER TABLE members ADD COLUMN status text NOT NULL DEFAULT 'active';

-- 2. A new default: only for rows inserted from now on.
ALTER TABLE members ALTER COLUMN status SET DEFAULT 'new';
INSERT INTO members (id, name, joined) VALUES (5, 'Alan', '2026-10-01');

-- 3. A new name for a column.
ALTER TABLE members RENAME COLUMN city TO town;
SELECT id, name, town, status FROM members ORDER BY id;

-- 4. Number new loans automatically, after the existing ids.
ALTER TABLE loans ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (START WITH 6);
INSERT INTO loans (book_id, member_id, loaned_on) VALUES (4, 5, '2026-10-02');
SELECT id, book_id, member_id, loaned_on FROM loans WHERE id >= 5 ORDER BY id;

setup.sql

CREATE TABLE members (
  id integer PRIMARY KEY,
  name text NOT NULL,
  email text UNIQUE,
  city text,
  joined date NOT NULL
);

CREATE TABLE books (
  id integer PRIMARY KEY,
  title text NOT NULL,
  author text,
  year integer,
  genre text,
  copies integer NOT NULL CHECK (copies >= 0)
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE loans (
  id integer PRIMARY KEY,
  book_id integer NOT NULL REFERENCES books (id),
  member_id integer NOT NULL REFERENCES members (id) ON DELETE CASCADE,
  loaned_on date NOT NULL,
  returned_on date,
  CHECK (returned_on >= loaned_on)
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

Ausführen mit

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Ausgabe

 id |   name   |  town  | status
----+----------+--------+--------
  1 | Ada      | Berlin | active
  2 | Grace    | Munich | active
  3 | Linus    | Berlin | active
  4 | Margaret |        | active
  5 | Alan     |        | new
(5 rows)

 id | book_id | member_id | loaned_on
----+---------+-----------+------------
  5 |       6 |         2 | 2026-09-18
  6 |       4 |         5 | 2026-10-02
(2 rows)
  • Die vier vorhandenen Mitglieder haben status active aus dem DEFAULT in ADD COLUMN bekommen; so lässt sich eine NOT-NULL-Spalte zu einer vollen Tabelle hinzufügen.
  • SET DEFAULT 'new' hat an diesen Zeilen nichts geändert. Nur Alan, danach ohne status eingefügt, hat new bekommen.
  • Nach dem Umbenennen stehen die Werte unter town. Margaret und Alan haben keine Stadt, als leere Zellen dargestellt.
  • Das INSERT in loans nennt keine id, und die Identity-Spalte vergibt 6, den Wert aus START WITH. Ohne ihn begänne der Zähler bei 1 und kollidierte mit Ausleihe 1.
Ändern und ausführen

Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.

Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.

Übungen

Übung 1 von 2

Ein Regal für jedes Buch

Jedes Buch braucht ein Regal (shelf). Die sechs Bücher, die schon da sind, stehen auf main; später hinzugefügte Bücher kommen zuerst auf new arrivals. Machen Sie shelf zu einer NOT-NULL-Spalte mit dem richtigen Standardwert für jede Gruppe, fügen Sie dann Persuasion (id 7, 1 Exemplar) ohne Regal hinzu und listen Sie id, title und shelf nach id auf. Der Startcode fügt die Spalte hinzu, aber alle Regale bleiben leer.

Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.

Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.

Hinweise
  1. Hinweis 1

    Ein Standardwert in ADD COLUMN füllt die vorhandenen Zeilen.

  2. Hinweis 2

    Ändern Sie dann den Standardwert mit ALTER COLUMN shelf SET DEFAULT …: Das berührt keine vorhandene Zeile.

  3. Hinweis 3

    ADD COLUMN shelf text NOT NULL DEFAULT 'main', dann SET DEFAULT 'new arrivals', dann das INSERT.

Eine Lösung zeigen

Ein möglicher Lösungsweg. Ihrer kann anders aussehen und trotzdem alle Prüfungen bestehen.

ALTER TABLE books ADD COLUMN shelf text NOT NULL DEFAULT 'main';
ALTER TABLE books ALTER COLUMN shelf SET DEFAULT 'new arrivals';
INSERT INTO books (id, title, copies) VALUES (7, 'Persuasion', 1);
SELECT id, title, shelf FROM books ORDER BY id;
Auf dem eigenen Computer ausführen

Installieren Sie PostgreSQL 18 oder neuer. Speichern Sie diese Dateien in einem Ordner, öffnen Sie dort ein Terminal und führen Sie die Befehle unten aus.

main.sql

-- Every book needs a shelf: the six old ones stand on 'main',
-- new books go to 'new arrivals' first.
ALTER TABLE books ADD COLUMN shelf text;
INSERT INTO books (id, title, copies) VALUES (7, 'Persuasion', 1);
SELECT id, title, shelf FROM books ORDER BY id;

test.sql

-- test: Die sechs alten Bücher stehen auf main, Persuasion auf new arrivals
-- output:
--  id |    title    |    shelf
-- ----+-------------+--------------
--   1 | Dune        | main
--   2 | Emma        | main
--   3 | Kindred     | main
--   4 | Beloved     | main
--   5 | The Hobbit  | main
--   6 | Neuromancer | main
--   7 | Persuasion  | new arrivals
-- (7 rows)

setup.sql

CREATE TABLE members (
  id integer PRIMARY KEY,
  name text NOT NULL,
  email text UNIQUE,
  city text,
  joined date NOT NULL
);

CREATE TABLE books (
  id integer PRIMARY KEY,
  title text NOT NULL,
  author text,
  year integer,
  genre text,
  copies integer NOT NULL CHECK (copies >= 0)
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE loans (
  id integer PRIMARY KEY,
  book_id integer NOT NULL REFERENCES books (id),
  member_id integer NOT NULL REFERENCES members (id) ON DELETE CASCADE,
  loaned_on date NOT NULL,
  returned_on date,
  CHECK (returned_on >= loaned_on)
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql führt die Dateien auf Ihrem eigenen PostgreSQL-Server aus. Nehmen Sie eine leere Datenbank, die Sie wegwerfen können: Hängen Sie -d und ihren Namen an den Befehl an.

Programm ausführen:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Für die Prüfungen gibt es auf Ihrem Computer noch keinen Befehl. Sie stehen in test.sql: Jede Prüfabfrage gibt t aus, wenn sie besteht.

Übung 2 von 2

Erst die Daten korrigieren, dann die Regel hinzufügen

Von jetzt an braucht jedes Mitglied eine E-Mail, und die Spalte city soll town heißen. Entfernen Sie das -- vor dem ALTER im Startcode und führen Sie ihn aus: Er scheitert, weil Linus keine E-Mail hat. Seine Adresse ist linus@example.com: Speichern Sie sie zuerst, fügen Sie dann die Regel hinzu und benennen Sie danach die Spalte um.

Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.

Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.

Hinweise
  1. Hinweis 1

    Lesen Sie den Fehler des Startcodes: column "email" of relation "members" contains null values. Welche Zeile ist es?

  2. Hinweis 2

    UPDATE members SET email = 'linus@example.com' WHERE id = 3; kommt zuerst.

  3. Hinweis 3

    Dann gelingt SET NOT NULL, und RENAME COLUMN city TO town ändert den Namen.

Eine Lösung zeigen

Ein möglicher Lösungsweg. Ihrer kann anders aussehen und trotzdem alle Prüfungen bestehen.

UPDATE members SET email = 'linus@example.com' WHERE id = 3;
ALTER TABLE members ALTER COLUMN email SET NOT NULL;
ALTER TABLE members RENAME COLUMN city TO town;
Auf dem eigenen Computer ausführen

Installieren Sie PostgreSQL 18 oder neuer. Speichern Sie diese Dateien in einem Ordner, öffnen Sie dort ein Terminal und führen Sie die Befehle unten aus.

main.sql

-- Every member needs an email from now on, and city becomes town.
-- ALTER TABLE members ALTER COLUMN email SET NOT NULL;
SELECT id, name, email FROM members ORDER BY id;

test.sql

-- test: email ist NOT NULL
SELECT attnotnull FROM pg_attribute WHERE attrelid = 'members'::regclass AND attname = 'email';

-- test: Linus hat seine E-Mail
SELECT email = 'linus@example.com' FROM members WHERE id = 3;

-- test: Die Spalte heißt town und enthält für Ada weiter Berlin
SELECT town = 'Berlin' FROM members WHERE id = 1;

-- test: Es gibt keine Spalte city mehr
SELECT NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'members'::regclass AND attname = 'city' AND NOT attisdropped);

-- test: Alle vier Mitglieder sind noch da
SELECT count(*) = 4 FROM members;

setup.sql

CREATE TABLE members (
  id integer PRIMARY KEY,
  name text NOT NULL,
  email text UNIQUE,
  city text,
  joined date NOT NULL
);

CREATE TABLE books (
  id integer PRIMARY KEY,
  title text NOT NULL,
  author text,
  year integer,
  genre text,
  copies integer NOT NULL CHECK (copies >= 0)
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE loans (
  id integer PRIMARY KEY,
  book_id integer NOT NULL REFERENCES books (id),
  member_id integer NOT NULL REFERENCES members (id) ON DELETE CASCADE,
  loaned_on date NOT NULL,
  returned_on date,
  CHECK (returned_on >= loaned_on)
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql führt die Dateien auf Ihrem eigenen PostgreSQL-Server aus. Nehmen Sie eine leere Datenbank, die Sie wegwerfen können: Hängen Sie -d und ihren Namen an den Befehl an.

Programm ausführen:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Für die Prüfungen gibt es auf Ihrem Computer noch keinen Befehl. Sie stehen in test.sql: Jede Prüfabfrage gibt t aus, wenn sie besteht.

Häufige Fehler

Eine Regel, die die vorhandenen Zeilen nicht erfüllen

ALTER TABLE members ALTER COLUMN email SET NOT NULL;

Was psql ausgibt

ERROR:  column "email" of relation "members" contains null values

Warum, und die Lösung

SET NOT NULL prüft wie ADD CONSTRAINT jede Zeile, die schon da ist. Finden Sie die Zeilen mit SELECT * FROM members WHERE email IS NULL; geben Sie ihnen mit UPDATE einen Wert (oder entscheiden Sie, dass die Regel falsch ist) und führen Sie das ALTER dann erneut aus.

Eine Identity-Spalte, die auf einer vollen Tabelle bei 1 beginnt

ALTER TABLE loans ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
INSERT INTO loans (book_id, member_id, loaned_on) VALUES (4, 4, '2026-10-02');

Was psql ausgibt

ERROR:  duplicate key value violates unique constraint "loans_pkey"

Warum, und die Lösung

Der neue Zähler schaut nicht auf die ids, die schon in der Tabelle sind: Er vergibt 1, und die hat Ausleihe 1. Beginnen Sie nach der höchsten id: ADD GENERATED BY DEFAULT AS IDENTITY (START WITH 6), oder versetzen Sie einen vorhandenen Zähler mit ALTER COLUMN id RESTART WITH 6.

Den Typ einer Spalte mit Standardwert ändern

ALTER TABLE books ADD COLUMN signed text DEFAULT 'no';
ALTER TABLE books ALTER COLUMN signed TYPE boolean USING signed = 'yes';

Was psql ausgibt

ERROR:  default for column "signed" cannot be cast automatically to type boolean

Warum, und die Lösung

USING wandelt die Zeilen um, nicht den Standardwert. Entfernen Sie zuerst den Standardwert und setzen Sie danach einen neuen: ALTER COLUMN signed DROP DEFAULT, dann die TYPE-Änderung, dann ALTER COLUMN signed SET DEFAULT false.

PostgreSQL im Browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 und PostgreSQL License. Lizenz und Quellcode

Abschlussquiz

5 Fragen, ohne Hinweise. Ab 80 % ist die Lektion abgeschlossen.

Erledigen Sie zuerst alle Aufgaben oben, um das Abschlussquiz freizuschalten.

Problem melden

Etwas ist falsch oder unklar? Beschreiben Sie es kurz, dann wird es geprüft und korrigiert.

#

Mindestens 20 Zeichen.

Nur, wenn Sie eine Antwort wünschen.

Kernideen

Hinzufügen, löschen und umbenennen

ALTER TABLE ändert eine Tabelle, die schon Zeilen enthält; Sie müssen sie also nicht löschen und neu anfangen. ADD COLUMN phone text fügt eine Spalte hinzu, die in jeder vorhandenen Zeile NULL ist. Mit einem DEFAULT bekommen die vorhandenen Zeilen stattdessen den Standardwert; deshalb funktioniert ADD COLUMN active boolean NOT NULL DEFAULT true auf einer vollen Tabelle. DROP COLUMN entfernt die Spalte und ihre Werte; braucht ein Fremdschlüssel einer anderen Tabelle sie, wie loans members.id braucht, lehnt PostgreSQL ab. RENAME COLUMN city TO town und RENAME TO patrons behalten die Daten, und Fremdschlüssel ziehen mit; der alte Name ist aber weg, also scheitert eine Abfrage, die noch city sagt.

Standardwerte, Typen und Regeln treffen auf die vorhandenen Zeilen

SET DEFAULT ändert nur, was spätere INSERTs bekommen; Zeilen, die schon in der Tabelle sind, behalten ihre Werte. DROP DEFAULT kehrt zu keinem Standardwert zurück, also NULL. TYPE ändert den Typ einer Spalte, wenn sich jeder Wert von selbst umwandeln lässt, etwa integer in bigint oder text in varchar(5), wenn jeder Wert hineinpasst. Sonst ergänzen Sie USING mit einem Ausdruck, der den neuen Wert berechnet: TYPE integer USING year::integer. SET NOT NULL und ADD CONSTRAINT prüfen sofort jede vorhandene Zeile und scheitern, wenn eine die Regel verletzt: column "email" of relation "members" contains null values. Korrigieren Sie zuerst die Daten mit UPDATE, dann fügen Sie die Regel hinzu.

Identity-Spalten nummerieren Zeilen für Sie

id integer GENERATED ALWAYS AS IDENTITY lässt PostgreSQL id aus einem Zähler füllen, 1, 2, 3 und so weiter, wann immer ein INSERT sie weglässt. Mit ALWAYS wird eine ausdrückliche id abgelehnt, außer das INSERT sagt OVERRIDING SYSTEM VALUE; mit BY DEFAULT gewinnt eine ausdrückliche id. Die Spalte ist NOT NULL, aber nicht von selbst eindeutig, also behalten Sie den PRIMARY KEY. Auf einer Tabelle mit Zeilen beginnt ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY ebenfalls bei 1, und das nächste INSERT kollidiert mit Ausleihe 1. Beginnen Sie stattdessen nach der höchsten id: ADD GENERATED BY DEFAULT AS IDENTITY (START WITH 6).

Quellen

Zuletzt geprüft am 4. Oktober 2026