DDL Syntax — CREATE, ALTER, DROP

-- Create a schema (idempotent variant uses IF NOT EXISTS).
CREATE SCHEMA reporting;
CREATE SCHEMA IF NOT EXISTS reporting AUTHORIZATION etl_writer;

-- Create a schema *and* its contents in one transactional unit.
-- The schema_element list accepts only CREATE TABLE / VIEW / INDEX / SEQUENCE / TRIGGER, and GRANT.
CREATE SCHEMA hollywood AUTHORIZATION joe
    CREATE TABLE films (title text, release date, awards text[])
    CREATE VIEW recent AS SELECT * FROM films WHERE release > CURRENT_DATE - 30
    GRANT SELECT ON films TO public;

-- Rename and reassign.
ALTER SCHEMA reporting RENAME TO analytics;
ALTER SCHEMA analytics OWNER TO data_eng;

-- Drop (RESTRICT is the default; CASCADE drops every contained object).
DROP SCHEMA analytics RESTRICT;
DROP SCHEMA IF EXISTS analytics CASCADE;

SHOW search_path;
SET search_path TO app, public;            -- session-wide
ALTER ROLE etl_writer SET search_path = etl, public;   -- role default
ALTER DATABASE app SET search_path = app, public;       -- DB default

CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ];

SELECT nextval('orders_id_seq');     -- advances and returns the next value
SELECT currval('orders_id_seq');     -- last value handed out *in this session* (error if never used here)
SELECT lastval();                    -- last value handed out by any nextval in this session
SELECT setval('orders_id_seq', 1000, true);   -- (next nextval -> 1001; pass false to mean "next nextval -> 1000")

ALTER SEQUENCE orders_id_seq RESTART WITH 1000000;     -- emergency reset (BEWARE: pre-existing rows above this value)
ALTER SEQUENCE orders_id_seq OWNED BY orders.id;       -- pair to a column so DROP COLUMN/DROP TABLE drops it too
ALTER SEQUENCE orders_id_seq SET LOGGED;               -- promote an UNLOGGED sequence (PG15+)
DROP SEQUENCE IF EXISTS legacy_id_seq CASCADE;

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name (
    [ column_name data_type [ COMPRESSION compression_method ] [ COLLATE collation ]
        [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ]
    ] [, ... ]
)
[ INHERITS ( parent_table [, ...] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) }
    [ COLLATE collation ] [ opclass ] [, ...] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ];

CREATE TABLE app.orders (
    id          bigint   GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint   NOT NULL REFERENCES app.customers (id),
    placed_at   timestamptz NOT NULL DEFAULT now(),
    status      text     NOT NULL CHECK (status IN ('open','paid','cancelled')),
    total_cents bigint   NOT NULL CHECK (total_cents >= 0)
);

[ CONSTRAINT constraint_name ]
{ NOT NULL
| NULL
| CHECK ( expression ) [ NO INHERIT ]
| DEFAULT default_expr
| GENERATED ALWAYS AS ( generation_expr ) STORED         -- STORED only on PG ≤ 17
| GENERATED ALWAYS AS ( generation_expr ) [ STORED | VIRTUAL ]   -- PG 18+
| GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
| UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters
| PRIMARY KEY index_parameters
| REFERENCES reftable [ ( refcolumn ) ]
    [ MATCH { FULL | PARTIAL | SIMPLE } ]
    [ ON DELETE referential_action ]
    [ ON UPDATE referential_action ]
}
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ];

-- Without NULLS NOT DISTINCT: two NULL rows are allowed.
-- With NULLS NOT DISTINCT: at most one NULL is allowed.
CREATE TABLE invites (
    email text,
    token text UNIQUE NULLS NOT DISTINCT
);

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ]
| UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ...] ) index_parameters
| PRIMARY KEY ( column_name [, ...] ) index_parameters
| EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ...] )
    index_parameters [ WHERE ( predicate ) ]
| FOREIGN KEY ( column_name [, ...] ) REFERENCES reftable [ ( refcolumn [, ...] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE referential_action ] [ ON UPDATE referential_action ]
}
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ];

PRIMARY KEY (id) WITH (fillfactor = 90)
PRIMARY KEY (id) USING INDEX TABLESPACE fast_ssd
UNIQUE (slug) INCLUDE (title, created_at)         -- covering, PG11+

-- PG18+ temporal primary key
CREATE TABLE rentals (
    room_id    int,
    booked_during tstzrange,
    PRIMARY KEY (room_id, booked_during WITHOUT OVERLAPS)
);

-- The standard pattern (PG10+):
CREATE TABLE app.events (
    id         bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    payload    jsonb  NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

-- Force the sequence value even when the column is GENERATED ALWAYS:
INSERT INTO events (id, payload) OVERRIDING SYSTEM VALUE VALUES (42, '{}'::jsonb);

-- Force the *sequence* to assign (instead of the user value) on a BY DEFAULT column:
INSERT INTO events (id, payload) OVERRIDING USER VALUE VALUES (NULL, '{}'::jsonb);

CREATE TABLE app.batches (
    id bigint GENERATED ALWAYS AS IDENTITY (
        SEQUENCE NAME app.batches_id_seq      -- PG15+ explicit name
        START WITH 100000
        INCREMENT BY 1
        CACHE 50
        NO CYCLE
    ),
    label text NOT NULL
);

SELECT setval(
    pg_get_serial_sequence('app.batches', 'id'),
    (SELECT max(id) FROM app.batches),
    true
);

-- Reattach the existing sequence as the identity sequence in a single transaction.
BEGIN;
ALTER TABLE old_table ALTER COLUMN id DROP DEFAULT;
ALTER TABLE old_table ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
-- The pre-existing old_table_id_seq is reused.
COMMIT;

CREATE TABLE app.invoices (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    subtotal    numeric(12,2) NOT NULL,
    tax         numeric(12,2) NOT NULL,
    total       numeric(12,2) GENERATED ALWAYS AS (subtotal + tax) STORED
);

-- PG18+
CREATE TABLE app.invoices (
    subtotal numeric(12,2),
    tax      numeric(12,2),
    total    numeric(12,2) GENERATED ALWAYS AS (subtotal + tax) VIRTUAL
);

CREATE TABLE app.articles (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text   NOT NULL,
    body  text   NOT NULL,
    fts   tsvector GENERATED ALWAYS AS (
              setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
              setweight(to_tsvector('english', coalesce(body, '')), 'B')
          ) STORED
);
CREATE INDEX articles_fts_gin ON app.articles USING gin (fts);

-- The parent ("partitioned table") declares the partition key.
CREATE TABLE app.measurements (
    sensor_id int  NOT NULL,
    ts        timestamptz NOT NULL,
    reading   double precision
) PARTITION BY RANGE (ts);

-- Child partitions can be created with PARTITION OF (takes ACCESS EXCLUSIVE on parent)…
CREATE TABLE app.measurements_2025q1 PARTITION OF app.measurements
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

-- …or created standalone then ATTACHed (takes SHARE UPDATE EXCLUSIVE on parent — preferred for online ops).
CREATE TABLE app.measurements_2025q2 (LIKE app.measurements INCLUDING ALL);
ALTER TABLE app.measurements_2025q2
    ADD CONSTRAINT chk_range CHECK (ts >= '2025-04-01' AND ts < '2025-07-01') NOT VALID;
ALTER TABLE app.measurements_2025q2 VALIDATE CONSTRAINT chk_range;
ALTER TABLE app.measurements ATTACH PARTITION app.measurements_2025q2
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

-- A DEFAULT partition catches values outside any declared range/list:
CREATE TABLE app.measurements_default PARTITION OF app.measurements DEFAULT;

-- 1. Build an invalid parent index that does not propagate.
CREATE INDEX measurements_ts_idx ON ONLY app.measurements (ts);

-- 2. Build each child index concurrently, in parallel transactions.
CREATE INDEX CONCURRENTLY measurements_2025q1_ts_idx ON app.measurements_2025q1 (ts);
CREATE INDEX CONCURRENTLY measurements_2025q2_ts_idx ON app.measurements_2025q2 (ts);

-- 3. Attach. Once every partition has the index, the parent index flips to valid.
ALTER INDEX app.measurements_ts_idx ATTACH PARTITION app.measurements_2025q1_ts_idx;
ALTER INDEX app.measurements_ts_idx ATTACH PARTITION app.measurements_2025q2_ts_idx;

CREATE TABLE staging.orders (LIKE app.orders INCLUDING ALL EXCLUDING IDENTITY);

CREATE TEMP TABLE session_cache (key text PRIMARY KEY, payload jsonb)
    ON COMMIT DELETE ROWS;

CREATE UNLOGGED TABLE fast_scratch (id bigint, body text);

ALTER TABLE app.hot_table SET (fillfactor = 80, autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE app.cold_table RESET (autovacuum_vacuum_scale_factor);

-- ADD COLUMN. Since PG11, adding a column with a non-volatile DEFAULT does NOT rewrite the table —
-- the default is stored in pg_attribute.attmissingval and applied on the fly.
ALTER TABLE app.orders ADD COLUMN priority int NOT NULL DEFAULT 0;

-- DROP COLUMN is logical: the column is hidden but the storage stays until table rewrite.
ALTER TABLE app.orders DROP COLUMN legacy_flag;

-- ALTER TYPE often rewrites the whole table. Use USING to convert values:
ALTER TABLE app.orders
    ALTER COLUMN total_cents TYPE numeric(14,2) USING total_cents::numeric / 100;

-- Toggle NOT NULL. Adding NOT NULL scans the whole table to confirm no nulls.
ALTER TABLE app.orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE app.orders ALTER COLUMN priority DROP NOT NULL;

-- Defaults are metadata-only.
ALTER TABLE app.orders ALTER COLUMN priority SET DEFAULT 5;
ALTER TABLE app.orders ALTER COLUMN priority DROP DEFAULT;

-- Per-column stats target.
ALTER TABLE app.orders ALTER COLUMN customer_id SET STATISTICS 1000;
ALTER TABLE app.orders ALTER COLUMN body SET (n_distinct = -0.5);

-- Per-column storage strategy.
ALTER TABLE app.articles ALTER COLUMN body SET STORAGE EXTERNAL;   -- PLAIN | EXTENDED | EXTERNAL | MAIN
ALTER TABLE app.articles ALTER COLUMN body SET COMPRESSION lz4;

-- Add a CHECK constraint without validating it (no scan, fast).
ALTER TABLE app.orders
    ADD CONSTRAINT chk_total_nonneg CHECK (total_cents >= 0) NOT VALID;

-- Validate it later (takes SHARE UPDATE EXCLUSIVE — concurrent reads/writes proceed).
ALTER TABLE app.orders VALIDATE CONSTRAINT chk_total_nonneg;

-- Drop a constraint.
ALTER TABLE app.orders DROP CONSTRAINT IF EXISTS chk_total_nonneg;

-- Rename a constraint or column or table.
ALTER TABLE app.orders RENAME CONSTRAINT chk_total_nonneg TO chk_total_nonnegative;
ALTER TABLE app.orders RENAME COLUMN total_cents TO total_minor_units;
ALTER TABLE app.orders RENAME TO orders_v2;

ALTER TABLE app.measurements DETACH PARTITION app.measurements_2024q4;
ALTER TABLE app.measurements DETACH PARTITION app.measurements_2024q4 CONCURRENTLY;
ALTER TABLE app.measurements DETACH PARTITION app.measurements_2024q4 FINALIZE;

ALTER TABLE app.measurements ATTACH PARTITION app.measurements_2025q3
    FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');

ALTER TABLE app.scratch SET UNLOGGED;        -- rewrites the table, ACCESS EXCLUSIVE
ALTER TABLE app.scratch SET LOGGED;          -- rewrites the table, ACCESS EXCLUSIVE
ALTER TABLE app.big SET TABLESPACE fast_ssd; -- rewrites the table; consider pg_repack for online moves
ALTER TABLE ALL IN TABLESPACE old_disk SET TABLESPACE new_disk NOWAIT;

ALTER TABLE app.audit_archive INHERIT app.audit;
ALTER TABLE app.audit_archive NO INHERIT app.audit;

ALTER TABLE app.orders OWNER TO app_writer;

-- REPLICA IDENTITY drives what's emitted for UPDATEs/DELETEs in logical replication.
-- DEFAULT  -> PK columns (recommended)
-- USING INDEX i -> a specific NOT NULL unique non-partial index
-- FULL     -> every column (heavy; only when there is no usable key)
-- NOTHING  -> UPDATEs/DELETEs are not decoded for this table (drop only)
ALTER TABLE app.orders REPLICA IDENTITY FULL;

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ];

-- Always inspect dependencies first:
SELECT classid::regclass, objid::regclass AS dependent
FROM pg_depend WHERE refobjid = 'app.orders'::regclass;

CREATE TABLE IF NOT EXISTS app.orders (...);
CREATE SCHEMA IF NOT EXISTS app;
CREATE SEQUENCE IF NOT EXISTS app.orders_id_seq;
CREATE INDEX IF NOT EXISTS orders_status_idx ON app.orders (status);

DROP TABLE IF EXISTS legacy.foo CASCADE;
DROP INDEX IF EXISTS bad_idx;
DROP SCHEMA IF EXISTS legacy CASCADE;

ALTER TABLE IF EXISTS app.orders ADD COLUMN priority int DEFAULT 0;
ALTER TABLE app.orders ADD COLUMN IF NOT EXISTS priority int DEFAULT 0;
ALTER TABLE app.orders DROP COLUMN IF EXISTS priority;
ALTER TABLE app.orders RENAME COLUMN IF EXISTS priority TO prio;     -- PG14+
ALTER TABLE app.orders DROP CONSTRAINT IF EXISTS chk_priority_range;

-- 1. Add the column nullable with a constant default. PG11+ keeps this metadata-only.
ALTER TABLE app.big ADD COLUMN tier int DEFAULT 0;

-- 2. Backfill in batches if the default isn't sufficient (e.g. derived from another column).
DO $$
DECLARE
    rows_updated int;
BEGIN
    LOOP
        UPDATE app.big SET tier = compute_tier(customer_id)
        WHERE ctid IN (
            SELECT ctid FROM app.big WHERE tier IS NULL LIMIT 10000 FOR UPDATE SKIP LOCKED
        );
        GET DIAGNOSTICS rows_updated = ROW_COUNT;
        EXIT WHEN rows_updated = 0;
        COMMIT;
    END LOOP;
END$$;

-- 3a. Portable across PG14–17: add a CHECK constraint, validate it, then promote.
--     (CHECK (col IS NOT NULL) is logically equivalent and supports NOT VALID + VALIDATE.)
ALTER TABLE app.big ADD CONSTRAINT big_tier_notnull CHECK (tier IS NOT NULL) NOT VALID;
ALTER TABLE app.big VALIDATE CONSTRAINT big_tier_notnull;
-- The CHECK can be left in place, or replaced with the proper NOT NULL via ALTER COLUMN SET NOT NULL —
-- with the CHECK already validated, SET NOT NULL still scans but the planner can use the CHECK to skip.

-- 3b. (PG18+) Set NOT NULL with NOT VALID directly on the column, then VALIDATE.
ALTER TABLE app.big ALTER COLUMN tier SET NOT NULL NOT VALID;
ALTER TABLE app.big VALIDATE CONSTRAINT big_tier_not_null;

-- Step 1: add the constraint, do not scan.
ALTER TABLE app.orders
    ADD CONSTRAINT chk_status CHECK (status IN ('open','paid','cancelled')) NOT VALID;

-- Step 2 (later, off-peak): validate.
ALTER TABLE app.orders VALIDATE CONSTRAINT chk_status;

BEGIN;
-- 1. Detach without blocking writes.
ALTER TABLE app.measurements DETACH PARTITION app.measurements_2024q4 CONCURRENTLY;
COMMIT;

-- 2. (optional) Re-add as standalone in archive schema.
ALTER TABLE app.measurements_2024q4 SET SCHEMA archive;

-- 3. (much later) Drop.
DROP TABLE archive.measurements_2024q4;

CREATE TABLE staging.orders (LIKE app.orders INCLUDING ALL EXCLUDING IDENTITY);
ALTER TABLE staging.orders ADD COLUMN id_orig bigint;
-- staging.orders now mirrors columns/constraints/indexes/defaults but its own id is plain bigint.

BEGIN;
ALTER TABLE app.legacy ALTER COLUMN id DROP DEFAULT;   -- detach from the SERIAL sequence
ALTER TABLE app.legacy ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;  -- reuses the existing seq
COMMIT;
-- The implicit sequence (app.legacy_id_seq) is now owned by the identity column.
-- pg_dump output will use the IDENTITY syntax instead of SERIAL.

-- Standard ALTER TYPE rewrites the table. To avoid the rewrite on a hot table:
-- 1. Add a new column.
ALTER TABLE app.events ADD COLUMN payload_v2 jsonb;

-- 2. Dual-write from the app layer; backfill in batches:
UPDATE app.events SET payload_v2 = payload::jsonb WHERE payload_v2 IS NULL AND id BETWEEN 1 AND 10000;
-- … repeat in batches …

-- 3. Cut over: rename in a single transaction with a short lock.
BEGIN;
ALTER TABLE app.events RENAME COLUMN payload    TO payload_legacy;
ALTER TABLE app.events RENAME COLUMN payload_v2 TO payload;
COMMIT;

-- 4. (later) Drop the legacy column.
ALTER TABLE app.events DROP COLUMN payload_legacy;

SET lock_timeout = '3s';
SET statement_timeout = '5min';

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema='app' AND table_name='orders' AND column_name='source'
    ) THEN
        ALTER TABLE app.orders ADD COLUMN source text;
    END IF;
END$$;

ALTER TABLE app.orders ADD COLUMN IF NOT EXISTS country_code char(2);
ALTER TABLE app.orders DROP CONSTRAINT IF EXISTS chk_legacy_status;
CREATE INDEX IF NOT EXISTS orders_status_idx ON app.orders (status);