DEFAULT-Werte und generierte Spalten

DEFAULT: Fehlende Werte automatisch ausfüllen

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Häufige DEFAULT-Werte

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
is_active BOOLEAN NOT NULL DEFAULT true
status TEXT NOT NULL DEFAULT 'pending'

DEFAULT nachträglich hinzufügen

ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- For existing rows, backfill manually:
UPDATE users SET status = 'active' WHERE status IS NULL;

NOT NULL mit DEFAULT hinzufügen

ALTER TABLE users
  ADD COLUMN signup_source TEXT NOT NULL DEFAULT 'web';
-- Fast in PG 11+, slow before

Berechnete generierte Spalten (STORED)

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  qty INT NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL,
  total NUMERIC(12,2) GENERATED ALWAYS AS (qty * unit_price) STORED
);

INSERT INTO orders (qty, unit_price) VALUES (3, 9.99);
SELECT * FROM orders;       -- total is auto-filled

Generierter tsvector für die Volltextsuche

ALTER TABLE articles
  ADD COLUMN search_doc tsvector
    GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX articles_search_idx ON articles USING GIN(search_doc);

Virtuelle generierte Spalten

In generierte Spalten können Sie nicht INSERTen

INSERT INTO orders (qty, unit_price, total) VALUES (3, 9.99, 30);
-- ERROR: column "total" can only be updated to DEFAULT

Sequenzbasierte Standardwerte

-- Roughly equivalent:
CREATE TABLE t (id BIGSERIAL PRIMARY KEY);

-- =>
CREATE SEQUENCE t_id_seq;
CREATE TABLE t (id BIGINT PRIMARY KEY DEFAULT nextval('t_id_seq'));
ALTER SEQUENCE t_id_seq OWNED BY t.id;

IDENTITY-Spalten

CREATE TABLE t (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT
);

-- GENERATED BY DEFAULT AS IDENTITY allows explicit overrides.

DEFAULT und Geschäftslogik

Zusammenfassung

Kurzer Test