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 图形化建表（新手友好）

右键 Schemas → public → Tables → Create → Table
填写：

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

Constraints 标签 → 添加 UNIQUE、CHECK
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 建表视频脚本？回复 视频脚本

随时告诉我！

PostgreSQL 创建表格

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

一、基本语法

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

三、核心约束（Constraints）

四、外键（Foreign Key）

五、索引（Index）提升性能

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

七、临时表 & 物化表

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

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

十、查看表结构

十一、修改表（ALTER TABLE）

十二、删除表

十三、常见错误与解决

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

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

小结：创建表格 5 步法

Linux 上安装 PostgreSQL

PostgreSQL pgAdmin 工具

PostgreSQL PRIVILEGES（权限）

PostgreSQL SELECT 语句

PostgreSQL LIMIT 子句

PostgreSQL 索引

发表回复取消回复

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

一、基本语法

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

三、核心约束（Constraints）

四、外键（Foreign Key）

五、索引（Index）提升性能

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

七、临时表 & 物化表

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

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

十、查看表结构

十一、修改表（ALTER TABLE）

十二、删除表

十三、常见错误与解决

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

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

小结：创建表格 5 步法

类似文章

发表回复 取消回复

发表回复取消回复