PostgreSQL CREATE TABLE

一、CREATE TABLE 基本语法

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

二、最基础示例

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

三、常用字段类型（核心）

1️⃣ 整数类型

类型	字节	说明
SMALLINT	2	小整数
INTEGER / INT	4	常用
BIGINT	8	大数据量

id BIGSERIAL

2️⃣ 自增主键（推荐方式）

PostgreSQL 10+（最佳实践）

id BIGINT GENERATED ALWAYS AS IDENTITY

或：

id BIGINT GENERATED BY DEFAULT AS IDENTITY

📌 区别：

ALWAYS：不能手动插入
BY DEFAULT：可手动覆盖

3️⃣ 字符串类型

类型	场景
VARCHAR(n)	有明确长度限制
TEXT	长度未知（推荐）
CHAR(n)	定长（不推荐）

title VARCHAR(100) NOT NULL
content TEXT

4️⃣ 时间类型（重点）

TIMESTAMP           -- 不带时区
TIMESTAMPTZ         -- 带时区（推荐）
DATE
TIME

created_at TIMESTAMPTZ NOT NULL DEFAULT now()

5️⃣ 布尔 / JSON

is_active BOOLEAN DEFAULT true
meta JSONB

四、列约束（Column Constraints）

1️⃣ NOT NULL

username TEXT NOT NULL

2️⃣ DEFAULT

created_at TIMESTAMPTZ DEFAULT now()

3️⃣ UNIQUE（单列）

email TEXT UNIQUE

4️⃣ CHECK

age INT CHECK (age >= 0)

5️⃣ REFERENCES（外键）

role_id INT REFERENCES roles(id)

五、表级约束（Table Constraints）

1️⃣ 主键

PRIMARY KEY (id)

2️⃣ 联合唯一约束（非常常见）

UNIQUE (code, url)

3️⃣ 联合外键

FOREIGN KEY (user_id, role_id)
REFERENCES user_roles(user_id, role_id)

六、完整实战建表示例（推荐写法）

CREATE TABLE articles (

    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    datetime TIMESTAMPTZ NOT NULL,

    code CHAR(6) NOT NULL,

    source TEXT NOT NULL,

    title VARCHAR(100) NOT NULL,

    url TEXT NOT NULL,

    CONSTRAINT uk_articles_code_url UNIQUE (code, url)
);

七、CREATE TABLE + 索引（最佳实践）

📌 表结构 ≠ 索引

建表后再建索引（推荐）

CREATE INDEX idx_articles_datetime ON articles (datetime);
CREATE INDEX idx_articles_code ON articles (code);

LIKE %xxx% 搜索

1️⃣ 启用扩展

CREATE EXTENSION IF NOT EXISTS pg_trgm;

2️⃣ GIN trigram 索引

CREATE INDEX idx_articles_source_trgm
ON articles USING gin (source gin_trgm_ops);

CREATE INDEX idx_articles_title_trgm
ON articles USING gin (title gin_trgm_ops);

CREATE INDEX idx_articles_url_trgm
ON articles USING gin (url gin_trgm_ops);

八、IF NOT EXISTS / TEMP / UNLOGGED

1️⃣ IF NOT EXISTS

CREATE TABLE IF NOT EXISTS logs (...);

2️⃣ 临时表

CREATE TEMP TABLE tmp_result (...);

会话结束自动删除

3️⃣ UNLOGGED（高性能，低安全）

CREATE UNLOGGED TABLE cache_data (...);

❌ 崩溃会丢数据
✅ 快

九、表注释（强烈推荐）

COMMENT ON TABLE articles IS '文章表';
COMMENT ON COLUMN articles.url IS '文章原始链接';

十、CREATE TABLE 常见坑

❌ 1️⃣ 用 CHAR 存可变字符串

code CHAR(6)  -- 会补空格

若有历史原因可以接受
否则优先 VARCHAR(6)

❌ 2️⃣ 所有字符串都用 VARCHAR(255)

PostgreSQL 中：

VARCHAR 和 TEXT 性能无差别

❌ 3️⃣ 忽略时区

日志 / 数据采集表：

TIMESTAMPTZ

十一、推荐建表“黄金模板”

CREATE TABLE xxx (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ,
    ...
);

十二、总结

CREATE TABLE 的关键不是“能建”，而是：

字段类型选对
约束设计清晰
索引独立规划
为未来查询模式服务