DEFAULT値と生成列

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()
);

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'

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

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

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

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

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);

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

-- 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;

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

-- GENERATED BY DEFAULT AS IDENTITY allows explicit overrides.