PostgreSQL 创建表格（Table）完全指南

涵盖 语法、数据类型、主键、外键、约束、索引、分区、权限、pgAdmin、脚本 等全部内容。

---

一、基本语法

CREATE TABLE table_name (
    column_name data_type [CONSTRAINTS],
    ...
);

---

二、完整推荐建表示例（生产级）

CREATE TABLE users (
    -- 主键：自增 ID
    id BIGSERIAL PRIMARY KEY,

    -- 文本字段
    username CITEXT NOT NULL UNIQUE,           -- 不区分大小写（需扩展）
    email CITEXT UNIQUE,
    password_hash TEXT NOT NULL,
    bio TEXT,

    -- 数字字段
    age SMALLINT CHECK (age >= 0 AND age <= 150),
    score INTEGER DEFAULT 0,

    -- 布尔与枚举
    is_active BOOLEAN DEFAULT true,
    role TEXT CHECK (role IN ('admin', 'user', 'guest')) DEFAULT 'user',

    -- 时间字段（推荐带时区）
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    last_login TIMESTAMPTZ,

    -- JSON 与数组
    profile JSONB DEFAULT '{}',
    tags TEXT[] DEFAULT '{}',

    -- 外键（后面详解）
    department_id BIGINT
);

---

三、核心约束（Constraints）

约束	语法	说明
NOT NULL	col TEXT NOT NULL	不能为空
UNIQUE	col TEXT UNIQUE	唯一值
PRIMARY KEY	id BIGSERIAL PRIMARY KEY	主键
CHECK	CHECK (age > 0)	条件校验
DEFAULT	DEFAULT now()	默认值
GENERATED ALWAYS AS	total NUMERIC GENERATED ALWAYS AS (qty * price) STORED	计算列

---

四、外键（Foreign Key）

CREATE TABLE departments (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- 在 users 表中添加外键
ALTER TABLE users 
ADD CONSTRAINT fk_department 
    FOREIGN KEY (department_id) 
    REFERENCES departments(id)
    ON DELETE SET NULL      -- 可选：删除时设为 NULL
    ON UPDATE CASCADE;      -- 更新时级联

常见策略：

• ON DELETE CASCADE：删除部门时删除用户
• ON DELETE SET NULL：设为 NULL
• ON DELETE RESTRICT：禁止删除（默认）

---

五、索引（Index）提升性能

-- 1. 单列索引（B树，默认）
CREATE INDEX idx_users_email ON users(email);

-- 2. 复合索引
CREATE INDEX idx_users_role_active ON users(role, is_active);

-- 3. 唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 4. JSONB 索引（GIN）
CREATE INDEX idx_users_profile ON users USING GIN (profile);

-- 5. 数组索引
CREATE INDEX idx_users_tags ON users USING GIN (tags);

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

---

六、分区表（Partitioning）—— 大表必备

-- 按时间分区（每月）
CREATE TABLE logs (
    id BIGSERIAL,
    message TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE logs_2025_11 PARTITION OF logs
    FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');

CREATE TABLE logs_2025_12 PARTITION OF logs
    FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');

优点：查询快、删除分区 = 秒删亿级数据

---

七、临时表 & 物化表

-- 临时表（会话结束自动删除）
CREATE TEMP TABLE temp_users AS 
SELECT * FROM users WHERE age > 30;

-- 物化表（缓存结果）
CREATE MATERIALIZED TABLE user_stats AS
SELECT department_id, COUNT(*) as cnt FROM users GROUP BY department_id;

-- 刷新
REFRESH MATERIALIZED VIEW user_stats;

---

八、pgAdmin 图形化建表（新手友好）

1. 右键 Schemas → public → Tables → Create → Table
2. 填写：

• Name：products
• Columns：
  • id → bigserial → Primary Key
  • name → text → Not Null
  • price → numeric(10,2)

1. Constraints 标签 → 添加 UNIQUE、CHECK
2. Save

---

九、创建表脚本（.sql 文件）

-- create_tables.sql
-- 运行：psql -U postgres -d mydb -f create_tables.sql

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 用户表
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username CITEXT NOT NULL UNIQUE,
    email CITEXT UNIQUE,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- 订单表
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    total NUMERIC(12,2) NOT NULL,
    status TEXT CHECK (status IN ('pending', 'paid', 'shipped')) DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT now()
);

-- 索引
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

运行：

psql -U postgres -d mydb -f create_tables.sql

---

十、查看表结构

-- psql 命令
\d tablename          -- 基本结构
\d+ tablename         -- 包含大小、描述
\dt                   -- 列出所有表
\di                   -- 列出索引

-- SQL 查询
SELECT column_name, data_type, is_nullable 
FROM information_schema.columns 
WHERE table_name = 'users';

---

十一、修改表（ALTER TABLE）

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 修改类型
ALTER TABLE users ALTER COLUMN age TYPE INTEGER;

-- 添加约束
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0);

-- 重命名表
ALTER TABLE users RENAME TO customers;

---

十二、删除表

DROP TABLE tablename CASCADE;  -- CASCADE 删除依赖

---

十三、常见错误与解决

错误	原因	解决
relation "users" already exists	表已存在	用 CREATE TABLE IF NOT EXISTS
type "citext" does not exist	扩展未启用	CREATE EXTENSION citext;
foreign key violation	外键数据不匹配	检查 REFERENCES 数据
check constraint violated	数据违反 CHECK	修正数据

---

十四、建表最佳实践（Checklist）

项目	建议
主键	BIGSERIAL 或 UUID
文本	TEXT > VARCHAR(n)
邮箱	CITEXT
时间	TIMESTAMPTZ
金额	NUMERIC(19,4)
状态	CHECK 约束或 ENUM
索引	频繁查询列、WHERE、JOIN
命名	小写 + 下划线，如 order_items
注释	COMMENT ON COLUMN users.bio IS '用户简介';

---

十五、一键建表示例（包含所有特性）

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 产品表（分区 + JSON + 数组 + 外键 + 计算列）
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL CHECK (price > 0),
    specs JSONB DEFAULT '{}',
    tags TEXT[] DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT now()
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE products_2025 PARTITION OF products
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- 索引
CREATE INDEX idx_products_specs ON products USING GIN (specs);
CREATE INDEX idx_products_tags ON products USING GIN (tags);

---

小结：创建表格 5 步法

1. 确定字段 + 类型（TEXT, TIMESTAMPTZ, JSONB）
2. 添加约束（NOT NULL, UNIQUE, CHECK）
3. 设置主键（BIGSERIAL / UUID）
4. 创建索引（WHERE/JOIN 列）
5. 启用扩展（citext, uuid-ossp）

---

现在就动手：
创建一个 blog_posts 表，包含：

• id（UUID）
• title（TEXT）
• content（TEXT）
• tags（TEXT[]）
• status（CHECK: draft/published）
• created_at（TIMESTAMPTZ）
• 加 GIN 索引

---

需要我生成：

• 完整电商数据库建表脚本？回复 电商脚本
• 自动插入 1000 条测试数据？回复 测试数据
• pgAdmin 建表视频脚本？回复 视频脚本

随时告诉我！