pgsql表结构定义

上面已经了解了一些基本的数据类型，pgsql里面支持很多数据类型核心数据类型 (Data Types) PostgreSQL 以其丰富的数据类型著称，以下是开发中最常用的几类：数值类型

上面已经了解了一些基本的数据类型，pgsql里面支持很多数据类型

核心数据类型 (Data Types)

PostgreSQL 以其丰富的数据类型著称，以下是开发中最常用的几类：

数值类型

INTEGER/INT: 4 字节整数，范围约$\pm21$ 亿。
BIGINT: 8 字节整数，用于大 ID 或大数据量计数。
NUMERIC(p, s)/DECIMAL: 精确的小数，用于金额。$p$是总位数，$s$ 是小数点后的位数。
SERIAL/BIGSERIAL: 自增整数（实际上是封装了SEQUENCE）。

字符类型

VARCHAR(n): 变长字符串，有长度限制。
TEXT: 变长字符串，无长度限制（PostgreSQL 推荐直接用这个，除非有业务硬性长度约束）。
CHAR(n): 定长字符串，不足部分补空格。

日期/时间类型

TIMESTAMP: 日期和时间。
TIMESTAMPTZ: 带时区的日期和时间（生产环境推荐使用，避免时区混乱）。
DATE: 仅日期。
INTERVAL: 时间间隔（如1 day 2 hours）。
tsrange: 对应timestamp without time zone 的范围。
tstzrange: 对应timestamp with time zone的范围（推荐用于跨时区业务）。
daterange: 对应date 的范围。

特色/高级类型

BOOLEAN:TRUE,FALSE, 或NULL。
JSONB: 二进制存储的 JSON 数据，支持索引，性能极佳。
UUID: 通用唯一识别码。
ARRAY: 数组类型，例如TEXT[] 可以存储标签列表。
INET：ip类型

列约束 (Column Constraints)

约束用于确保数据的准确性和可靠性（实体完整性、参照完整性）。

约束类型	描述	示例
`NOT NULL`	强制列不能包含`NULL` 值。	`name TEXT NOT NULL`
`UNIQUE`	确保列中的所有值互不相同。	`email TEXT UNIQUE`
`PRIMARY KEY`	主键，唯一标识每一行。隐含`NOT NULL`和`UNIQUE`。	`id SERIAL PRIMARY KEY`
`FOREIGN KEY`	外键，建立表与表之间的链接，防止破坏关系。	`user_id INT REFERENCES users(id)`
`CHECK`	检查值是否满足特定逻辑条件。	`age INT CHECK (age >= 18)`
`DEFAULT`	如果插入时未指定值，则使用默认值。	`created_at TIMESTAMPTZ DEFAULT NOW()`

示例

CREATE TABLE public.file_details (
    -- 1. 自增长整数主键 (BIGSERIAL)
    -- 知识点：BIGSERIAL 是 8 字节整数，支持极大数量级（达 922 亿亿次插入）
    -- 适合作为数据库内部的关联外键，性能优于 UUID
    id BIGSERIAL PRIMARY KEY,

    -- 2. 现代唯一标识 (UUID)
    -- 知识点：对外暴露的 ID，防止业务数据量泄露
    detail_id UUID UNIQUE DEFAULT gen_random_uuid(),

    -- 3. 时间范围类型 (TSTZRANGE)
    -- 知识点：存储文件的“有效期”起止时间。
    -- 例如：'[2023-01-01, 2024-01-01)' 表示从元旦开始，一年内有效。
    valid_period TSTZRANGE NOT NULL DEFAULT tstzrange(now(), NULL, '[)'),

    -- 4. 空间与网络 (INET)
    uploader_ip INET NOT NULL,

    -- 5. 集合容器 (ARRAY)
    tags TEXT[] DEFAULT '{}',

    -- 6. 半结构化神器 (JSONB)
    metadata JSONB DEFAULT '{}',

    -- 基础字段
    file_name TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

插入数据

INSERT INTO public.file_details (uploader_ip, tags, metadata, file_name, valid_period)
VALUES 
    -- 1. 文档：标准构造函数
    ('10.0.0.5', '{"PDF", "文档"}', '{"pages": 45, "author": "Fengfeng"}', 'PostgreSQL手册.pdf', 
     tstzrange('2026-01-01 00:00:00Z', '2027-01-01 00:00:00Z', '[)')),

    -- 2. 图片：修正字符串格式 (去掉逗号后的空格)
    -- 注意：'[start,]' 表示包含起点，无终点
    ('127.0.0.1', '{"素材", "封面"}', '{"dpi": 300, "color": "RGB"}', 'bilibili横屏封面.png', 
     '[2026-04-14 08:00:00+08,]'),

    -- 3. 简单文件：另一种写法，使用 NULL 表示无穷大
    ('172.16.0.100', '{}', '{}', 'test_file.txt', 
     tstzrange('2025-01-01', NULL, '[)')),

    -- 4. 视频文件：直接使用 DEFAULT
    ('192.168.50.20', '{"视频"}', '{"video": {"resolution": {"width": 1920}}}', 'test_file.mp4', 
     DEFAULT);