2.1 SQL 基础 - DDL 数据定义语言

一、DDL 概述

DDL（Data Definition Language，数据定义语言）用于定义和管理数据库结构，包括数据库、模式、表、索引、视图等对象的创建、修改和删除。

DDL 的特点：

• 执行后自动提交事务（在 PostgreSQL 中，DDL 不是完全自动提交的）
• 操作对象是数据库结构，而非数据本身
• 通常需要较高的权限

PostgreSQL 与其他数据库 DDL 的区别：

• PostgreSQL 的 DDL 支持事务回滚（可以 BEGIN/ROLLBACK DDL 操作）
• 支持 CREATE IF NOT EXISTS 语法
• 支持 ALTER TABLE 的丰富操作

二、数据库操作

1. 创建数据库

-- 基础语法
CREATE DATABASE database_name;

-- 完整语法
CREATE DATABASE database_name
    [ OWNER = user_name ]
    [ TEMPLATE = template_name ]
    [ ENCODING = encoding_name ]
    [ LC_COLLATE = collate_name ]
    [ LC_CTYPE = ctype_name ]
    [ TABLESPACE = tablespace_name ]
    [ CONNECTION LIMIT = limit ];

-- 示例：创建数据库
CREATE DATABASE myapp;

-- 指定所有者
CREATE DATABASE myapp OWNER = app_user;

-- 指定编码和排序规则
CREATE DATABASE myapp 
    ENCODING = 'UTF8' 
    LC_COLLATE = 'zh_CN.UTF-8' 
    LC_CTYPE = 'zh_CN.UTF-8';

-- 使用模板创建
CREATE DATABASE myapp TEMPLATE = template0;

-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS myapp;

-- 设置连接数限制
CREATE DATABASE myapp CONNECTION LIMIT = 100;

2. 查看数据库

-- 查看所有数据库
\l
SELECT datname FROM pg_database;

-- 查看数据库详细信息
\l+
SELECT 
    datname,
    datdba::regrole AS owner,
    pg_encoding_to_char(encoding) AS encoding,
    datcollate AS collate,
    datctype AS ctype,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database;

-- 查看当前数据库
SELECT current_database();

-- 切换数据库
\c myapp
-- 或
CONNECT TO myapp;

3. 修改数据库

-- 重命名数据库
ALTER DATABASE myapp RENAME TO myapp_v2;

-- 修改所有者
ALTER DATABASE myapp OWNER TO new_owner;

-- 修改配置参数
ALTER DATABASE myapp SET work_mem = '16MB';
ALTER DATABASE myapp SET timezone TO 'Asia/Shanghai';

-- 重置配置参数
ALTER DATABASE myapp RESET work_mem;

-- 修改连接限制
ALTER DATABASE myapp CONNECTION LIMIT 200;

-- 允许/禁止连接
ALTER DATABASE myapp ALLOW_CONNECTIONS = false;
ALTER DATABASE myapp ALLOW_CONNECTIONS = true;

-- 修改默认表空间
ALTER DATABASE myapp SET TABLESPACE = new_tablespace;

4. 删除数据库

-- 删除数据库（必须没有连接）
DROP DATABASE myapp;

-- 如果存在则删除
DROP DATABASE IF EXISTS myapp;

-- 强制断开所有连接并删除
-- 方法1：先终止连接
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'myapp' AND pid <> pg_backend_pid();

-- 然后删除
DROP DATABASE myapp;

-- 方法2：使用 DROP DATABASE WITH (FORCE)
-- PostgreSQL 13+ 支持
DROP DATABASE myapp WITH (FORCE);

三、模式（Schema）操作

Schema 是数据库内的命名空间，用于组织数据库对象。

1. 创建模式

-- 基础语法
CREATE SCHEMA schema_name;

-- 指定所有者
CREATE SCHEMA schema_name AUTHORIZATION user_name;

-- 如果不存在则创建
CREATE SCHEMA IF NOT EXISTS schema_name;

-- 创建模式并设置默认权限
CREATE SCHEMA sales 
    AUTHORIZATION sales_user 
    CREATE TABLE sales.orders (id int);

-- 示例
CREATE SCHEMA app;
CREATE SCHEMA IF NOT EXISTS logging AUTHORIZATION log_user;

2. 模式搜索路径

-- 查看当前搜索路径
SHOW search_path;
-- 默认："$user", public

-- 设置搜索路径（会话级）
SET search_path TO app, public;

-- 设置搜索路径（数据库级）
ALTER DATABASE myapp SET search_path TO app, public;

-- 设置搜索路径（用户级）
ALTER ROLE app_user SET search_path TO app, public;

-- 永久设置（配置文件）
-- search_path = '"$user", app, public'

3. 查看和管理模式

-- 查看所有模式
\dn
SELECT schema_name FROM information_schema.schemata;

-- 查看模式详细信息
\dn+
SELECT 
    nspname AS schema,
    nspowner::regrole AS owner,
    pg_size_pretty(pg_total_relation_size(nspname)) AS total_size
FROM pg_namespace
ORDER BY total_size DESC NULLS LAST;

-- 修改模式所有者
ALTER SCHEMA app OWNER TO new_owner;

-- 重命名模式
ALTER SCHEMA app RENAME TO application;

-- 删除模式
DROP SCHEMA app;
DROP SCHEMA app CASCADE;  -- 级联删除其中的所有对象
DROP SCHEMA IF EXISTS app CASCADE;

4. 模式权限

-- 授予使用权限
GRANT USAGE ON SCHEMA app TO app_user;

-- 授予创建对象权限
GRANT CREATE ON SCHEMA app TO app_user;

-- 授予所有权限
GRANT ALL ON SCHEMA app TO app_user;

-- 撤销权限
REVOKE CREATE ON SCHEMA app FROM app_user;

-- 设置默认权限（后续创建的对象自动继承）
ALTER DEFAULT PRIVILEGES IN SCHEMA app 
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

四、表操作

1. 创建表

-- 基础语法
CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    [table_constraints]
);

-- 示例：用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

-- 示例：订单表（带外键）
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    total_amount NUMERIC(10,2) NOT NULL CHECK (total_amount >= 0),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 示例：带复合主键
CREATE TABLE order_items (
    order_id BIGINT NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price NUMERIC(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- 从查询结果创建表
CREATE TABLE active_users AS 
SELECT id, username, email, created_at 
FROM users 
WHERE status = 'active';

-- 创建临时表（会话结束后自动删除）
CREATE TEMP TABLE temp_data (
    id SERIAL,
    data TEXT
);

-- 创建未记录日志的表（性能更高，但崩溃时数据会丢失）
CREATE UNLOGGED TABLE cache_table (
    key VARCHAR(100) PRIMARY KEY,
    value TEXT,
    expires_at TIMESTAMP
);

2. 数据类型速查表

3. 修改表结构

-- 添加列
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50);
ALTER TABLE users ADD COLUMN last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE users ADD COLUMN IF NOT EXISTS nickname VARCHAR(50);

-- 删除列
ALTER TABLE users DROP COLUMN middle_name;
ALTER TABLE users DROP COLUMN IF EXISTS nickname CASCADE;  -- CASCADE 会删除依赖对象

-- 修改列数据类型
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(20);
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(20) USING phone::VARCHAR(20);

-- 修改列名
ALTER TABLE users RENAME COLUMN phone TO mobile;

-- 设置/删除默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'pending';
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- 设置/删除 NOT NULL 约束
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

-- 添加约束
ALTER TABLE users ADD CONSTRAINT unique_username UNIQUE (username);
ALTER TABLE users ADD CONSTRAINT age_check CHECK (age >= 0 AND age <= 150);
ALTER TABLE users ADD PRIMARY KEY (id);

-- 添加外键约束
ALTER TABLE orders ADD CONSTRAINT fk_user 
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- 删除约束
ALTER TABLE users DROP CONSTRAINT unique_username;
ALTER TABLE users DROP CONSTRAINT users_pkey CASCADE;

-- 重命名表
ALTER TABLE users RENAME TO app_users;
ALTER TABLE users RENAME COLUMN username TO user_name;

-- 设置表的所属模式
ALTER TABLE users SET SCHEMA app;

-- 设置表的所有者
ALTER TABLE users OWNER TO app_user;

-- 修改表存储参数
ALTER TABLE users SET (fillfactor = 80);
ALTER TABLE users SET (autovacuum_enabled = false);

-- 修改表的表空间
ALTER TABLE users SET TABLESPACE fast_ssd;

4. 删除表

-- 删除表
DROP TABLE users;

-- 如果存在则删除
DROP TABLE IF EXISTS users;

-- 级联删除（删除依赖该表的对象，如视图、外键等）
DROP TABLE users CASCADE;

-- 删除多个表
DROP TABLE users, orders, products CASCADE;

5. 查看表信息

-- 列出所有表
\d
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

-- 查看表结构
\d users
\d+ users  -- 详细信息

-- 查看表信息（系统视图）
SELECT 
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM information_schema.columns 
WHERE table_name = 'users';

-- 查看约束
SELECT 
    constraint_name,
    constraint_type,
    check_clause
FROM information_schema.table_constraints tc
JOIN information_schema.check_constraints cc 
    ON tc.constraint_name = cc.constraint_name
WHERE table_name = 'users';

五、约束详解

1. 约束类型

2. 约束示例

-- 复合约束示例
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(20) NOT NULL,
    name VARCHAR(200) NOT NULL,
    category_id INTEGER,
    price NUMERIC(10,2) CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 复合唯一约束
    CONSTRAINT unique_sku_per_category UNIQUE (category_id, sku),
    -- 外键约束
    CONSTRAINT fk_category FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 命名约束（推荐）
CREATE TABLE employees (
    id SERIAL,
    emp_no VARCHAR(20) NOT NULL,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER,
    salary NUMERIC(10,2),
    hire_date DATE,
    CONSTRAINT pk_employees PRIMARY KEY (id),
    CONSTRAINT uk_emp_no UNIQUE (emp_no),
    CONSTRAINT ck_salary_positive CHECK (salary > 0),
    CONSTRAINT ck_hire_date CHECK (hire_date <= CURRENT_DATE),
    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 延迟约束（事务结束时检查）
CREATE TABLE schedule (
    id SERIAL PRIMARY KEY,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    -- 延迟约束，事务结束前都可以修改
    CONSTRAINT chk_time_range CHECK (start_time < end_time) DEFERRABLE INITIALLY IMMEDIATE
);

-- 排他约束（防止重叠时间）
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    EXCLUDE USING gist (
        room_id WITH =,
        tstzrange(start_time, end_time) WITH &&
    )
);

3. 约束管理

-- 添加命名约束
ALTER TABLE products ADD CONSTRAINT positive_price CHECK (price >= 0);

-- 添加 NOT NULL 约束（特殊语法）
ALTER TABLE products ALTER COLUMN name SET NOT NULL;

-- 删除约束
ALTER TABLE products DROP CONSTRAINT positive_price;

-- 暂时禁用约束（仅外键）
ALTER TABLE orders DISABLE TRIGGER ALL;  -- 临时禁用所有触发器
-- 执行批量操作...
ALTER TABLE orders ENABLE TRIGGER ALL;

-- 修改约束为延迟检查
ALTER TABLE schedule ALTER CONSTRAINT chk_time_range DEFERRABLE INITIALLY DEFERRED;

-- 查看约束是否延迟
SELECT conname, condeferrable, condeferred
FROM pg_constraint 
WHERE conrelid = 'schedule'::regclass;

六、索引操作

1. 创建索引

-- B-Tree 索引（默认）
CREATE INDEX idx_users_email ON users(email);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_username_unique ON users(username);

-- 复合索引
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- 部分索引
CREATE INDEX idx_active_users_email ON users(email) WHERE status = 'active';

-- 覆盖索引（INCLUDE 列）
CREATE INDEX idx_users_covering ON users(id) INCLUDE (username, email);

-- GIN 索引（用于 JSONB、数组、全文搜索）
CREATE INDEX idx_products_tags ON products USING gin(tags);
CREATE INDEX idx_products_metadata ON products USING gin(metadata);

-- GIST 索引（地理空间、范围类型）
CREATE INDEX idx_locations_coords ON locations USING gist(coords);
CREATE INDEX idx_reservations_time ON reservations USING gist(tstzrange(start_time, end_time));

-- BRIN 索引（大表、自然排序）
CREATE INDEX idx_logs_created_at ON logs USING brin(created_at);

-- 哈希索引
CREATE INDEX idx_users_phone_hash ON users USING hash(phone);

-- 并发创建索引（不阻塞写操作）
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- 创建索引并指定表空间
CREATE INDEX idx_users_email ON users(email) TABLESPACE fast_ssd;

2. 索引管理

-- 查看索引
\di
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

-- 查看索引大小
SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes 
WHERE tablename = 'users';

-- 重建索引
REINDEX INDEX idx_users_email;
REINDEX TABLE users;  -- 重建表的所有索引
REINDEX DATABASE myapp;  -- 重建数据库的所有索引
REINDEX INDEX CONCURRENTLY idx_users_email;  -- 不阻塞写入

-- 删除索引
DROP INDEX idx_users_email;
DROP INDEX IF EXISTS idx_users_email CASCADE;

-- 设置索引的填充因子
CREATE INDEX idx_users_email ON users(email) WITH (fillfactor = 90);
ALTER INDEX idx_users_email SET (fillfactor = 90);

3. 索引使用建议

-- 查看索引使用情况
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan AS times_used,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan;

-- 查找未使用的索引
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexname::regclass) DESC;

-- 查找重复索引
SELECT 
    indexrelid::regclass AS index_name,
    indrelid::regclass AS table_name,
    array_to_string(indkey, ' ') AS columns_oids,
    array_agg(indexrelid::regclass) OVER (PARTITION BY indrelid, indkey) AS duplicate_indexes
FROM pg_index
WHERE indrelid::regclass = 'users'::regclass;

七、视图操作

1. 普通视图

-- 创建视图
CREATE VIEW active_users AS
SELECT id, username, email, created_at
FROM users
WHERE status = 'active';

-- 创建带列别名
CREATE VIEW user_summary (user_id, user_name, email_address) AS
SELECT id, username, email
FROM users;

-- 创建视图并设置安全屏障（防止利用规则获取敏感数据）
CREATE VIEW safe_users WITH (security_barrier) AS
SELECT id, username, status
FROM users
WHERE status != 'deleted';

-- 查看视图定义
\d+ active_users
SELECT view_definition FROM information_schema.views WHERE view_name = 'active_users';

-- 创建递归视图
CREATE RECURSIVE VIEW employee_tree (id, name, manager_id, level) AS
SELECT id, name, manager_id, 1
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, et.level + 1
FROM employees e
JOIN employee_tree et ON e.manager_id = et.id;

-- 更新视图（简单视图可更新）
INSERT INTO active_users (username, email) VALUES ('newuser', 'new@example.com');

-- 创建可更新视图的规则
CREATE RULE update_active_users AS
ON UPDATE TO active_users
DO INSTEAD
UPDATE users SET 
    username = NEW.username,
    email = NEW.email,
    status = NEW.status
WHERE id = OLD.id;

-- 删除视图
DROP VIEW active_users;
DROP VIEW IF EXISTS active_users CASCADE;

2. 物化视图

-- 创建物化视图（存储实际数据）
CREATE MATERIALIZED VIEW order_stats AS
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_spent,
    AVG(total_amount) AS avg_order
FROM orders
WHERE status = 'completed'
GROUP BY user_id;

-- 创建带索引的物化视图
CREATE MATERIALIZED VIEW user_metrics WITH (fillfactor = 90) AS
SELECT ...;
CREATE INDEX idx_metrics_user ON user_metrics(user_id);

-- 刷新物化视图
REFRESH MATERIALIZED VIEW order_stats;  -- 会锁定表
REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats;  -- 不锁定，需要唯一索引

-- 并行刷新（需要先创建唯一索引）
CREATE UNIQUE INDEX idx_order_stats_user ON order_stats(user_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats;

-- 查看物化视图
\d+ order_stats

-- 删除物化视图
DROP MATERIALIZED VIEW order_stats;
DROP MATERIALIZED VIEW IF EXISTS order_stats CASCADE;

八、序列操作

1. 创建和管理序列

-- 创建序列
CREATE SEQUENCE user_id_seq START 1000 INCREMENT 1 MINVALUE 1 MAXVALUE 999999;

-- 创建序列并指定缓存
CREATE SEQUENCE order_seq CACHE 100;

-- 创建循环序列
CREATE SEQUENCE cycle_seq MINVALUE 1 MAXVALUE 10 CYCLE;

-- 使用序列
SELECT nextval('user_id_seq');     -- 获取下一个值
SELECT currval('user_id_seq');     -- 获取当前值
SELECT lastval();                  -- 获取最近使用的序列值
SELECT setval('user_id_seq', 2000);        -- 设置当前值
SELECT setval('user_id_seq', 2000, false); -- 设置当前值，下次 nextval 返回 2000

-- 在表中使用序列
CREATE TABLE users (
    id INTEGER PRIMARY KEY DEFAULT nextval('user_id_seq'),
    name TEXT
);

-- SERIAL 类型的本质就是序列
CREATE TABLE products (
    id SERIAL PRIMARY KEY,  -- 等价于上面的写法
    name TEXT
);

-- 修改序列
ALTER SEQUENCE user_id_seq RESTART WITH 5000;
ALTER SEQUENCE user_id_seq INCREMENT BY 5;
ALTER SEQUENCE user_id_seq MAXVALUE 1000000;

-- 查看序列信息
SELECT * FROM information_schema.sequences WHERE sequence_name = 'user_id_seq';

-- 删除序列
DROP SEQUENCE user_id_seq;
DROP SEQUENCE IF EXISTS user_id_seq CASCADE;

2. 身份列（PostgreSQL 10+）

-- 使用 GENERATED AS IDENTITY
CREATE TABLE employees (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

-- 允许覆盖（GENERATED BY DEFAULT）
CREATE TABLE employees (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1000) PRIMARY KEY,
    name TEXT NOT NULL
);

-- 覆盖身份列值
INSERT INTO employees (id, name) VALUES (999, 'Special User');

-- 重置身份列
ALTER TABLE employees ALTER COLUMN id RESTART WITH 2000;

九、表空间操作

-- 创建表空间
CREATE TABLESPACE fast_ssd LOCATION '/mnt/ssd/postgres/data';
CREATE TABLESPACE slow_hdd LOCATION '/mnt/hdd/postgres/data';

-- 创建表空间并指定所有者
CREATE TABLESPACE archive_space OWNER postgres LOCATION '/mnt/archive/postgres';

-- 在表空间中创建对象
CREATE TABLE large_table (id SERIAL, data TEXT) TABLESPACE fast_ssd;
CREATE INDEX idx_large ON large_table(data) TABLESPACE fast_ssd;

-- 设置默认表空间
SET default_tablespace = fast_ssd;

-- 移动表到另一个表空间
ALTER TABLE large_table SET TABLESPACE slow_hdd;

-- 查看表空间
\db
SELECT spcname, pg_tablespace_location(oid), pg_size_pretty(pg_tablespace_size(spcname))
FROM pg_tablespace;

-- 删除表空间（必须为空）
DROP TABLESPACE fast_ssd;

十、DDL 最佳实践

1. 命名规范

-- 推荐命名规范
-- 表名：小写，下划线分隔，复数或单数保持一致
CREATE TABLE user_profiles (...);

-- 主键：pk_表名
ALTER TABLE users ADD CONSTRAINT pk_users PRIMARY KEY (id);

-- 外键：fk_子表_父表
ALTER TABLE orders ADD CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users(id);

-- 唯一约束：uk_表名_字段名
ALTER TABLE users ADD CONSTRAINT uk_users_email UNIQUE (email);

-- 检查约束：ck_表名_含义
ALTER TABLE products ADD CONSTRAINT ck_products_price_positive CHECK (price >= 0);

-- 索引：idx_表名_字段名
CREATE INDEX idx_users_email ON users(email);

-- 序列：seq_表名_字段名
CREATE SEQUENCE seq_users_id;

2. 迁移策略

-- 安全的表结构变更
BEGIN;

-- 1. 添加新列（允许 NULL）
ALTER TABLE orders ADD COLUMN new_column VARCHAR(100);

-- 2. 逐步填充数据
UPDATE orders SET new_column = old_column WHERE new_column IS NULL;

-- 3. 设置 NOT NULL
ALTER TABLE orders ALTER COLUMN new_column SET NOT NULL;

-- 4. 添加默认值
ALTER TABLE orders ALTER COLUMN new_column SET DEFAULT 'default';

COMMIT;

-- 零停机时间添加列（使用系统目录）
-- PostgreSQL 11+ 添加没有默认值的列是 O(1) 操作
ALTER TABLE huge_table ADD COLUMN new_col INTEGER;

3. DDL 回滚

-- 利用事务回滚 DDL（PostgreSQL 独有特性）
BEGIN;

CREATE TABLE test_rollback (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO test_rollback (name) VALUES ('test');

-- 误操作...
DROP TABLE test_rollback;

-- 回滚！
ROLLBACK;

-- test_rollback 表仍然存在
SELECT * FROM test_rollback;

4. 避免锁表的最佳实践

-- 使用 CONCURRENTLY 创建索引
CREATE INDEX CONCURRENTLY idx_big_table ON big_table(column);

-- 使用 NOT VALID 添加约束
ALTER TABLE users ADD CONSTRAINT ck_age CHECK (age >= 0) NOT VALID;
-- 后台验证
ALTER TABLE users VALIDATE CONSTRAINT ck_age;

-- 批量 DDL 操作分批执行
DO $$
DECLARE
    i RECORD;
BEGIN
    FOR i IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
    LOOP
        EXECUTE format('ALTER TABLE %I ADD COLUMN created_by TEXT', i.tablename);
    END LOOP;
END $$;