PostgreSQL 数据类型

数据类型决定一列能够保存什么数据，同时影响：

数据准确性
存储空间
查询性能
索引效率
Python 类型映射
数据库约束能力

PostgreSQL 的类型系统非常丰富，除了常见的数字、文本、时间，还原生支持 jsonb、数组、UUID、IP 地址、范围和全文检索等类型。PostgreSQL 18 数据类型文档

一、常用类型速查

数据内容	推荐类型	示例
小整数	`smallint`	小数字
普通整数	`integer`	数量、状态值
大整数	`bigint`	主键、成家量
精确小数	`numeric(p, s)`	股价、金额、比例
科学计算小数	`double precision`	指标、测量结果
普通字符串	`text`	公司名称、描述
限制长度的字符串	`varchar(n)`	股票代码
布尔值	`boolean`	是否ST、是否启用
日期	`date`	交易日期
时间点	`timestamptz`	行情时间、创建时间
时间长度	`interval`	5分钟、30天
JSON 数据	`jsonb`	接口原始数据、扩展属性
唯一标识	`uuid`	订单ID、分布式主键
二进制数据	`bytea`	文件内容、哈希值
IP 地址	`inet`	客户端IP
枚举	`ENUM`	固定的一组数据
一组同类型值	数组	标签、权限集合

二、整数类型

PostgreSQL 提供三种整数：

类型	存储	范围	使用场景
`smallint`	2字节	-32768～32767	很小的状态值
`integer`	4字节	约 ±21亿	普通整数
`bigint`	8字节	约 ±922亿亿	主键、成交量、大计数

CREATE TABLE example_integer (
    status      smallint,
    quantity    integer,
    volume      bigint
);

实际选择原则：

默认使用 integer
可能超过21亿时使用 bigint
不必为了节省少量空间滥用 smallint

自增主键

传统写法：

CREATE TABLE users (
    id bigserial PRIMARY KEY
);

现代推荐写法：

CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

如果需要手动插入 ID，可以使用：

CREATE TABLE users (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
);

区别：

ALWAYS：原则上只能由数据库生成
BY DEFAULT：应用可以显式提供 ID
serial、bigserial 不是真正的数据类型，本质是整数列加序列
序列出现跳号是正常现象，事务回滚不会归还已经取出的序列值

三、精确小数

1. `numeric` 和 `decimal`

两者完全等价：

numeric(precision, scale)
decimal(precision, scale)

例如：

price numeric(18, 4)

其中：

18：最多18位有效数字
4：小数点后最多4位
小数点左侧最多14位

适合保存：

股价
金额
收益率
财务指标
需要精确计算的数据

CREATE TABLE daily_quote (
    code         varchar(6)   NOT NULL,
    trade_date   date         NOT NULL,
    open_price   numeric(18,4),
    high_price   numeric(18,4),
    low_price    numeric(18,4),
    close_price  numeric(18,4),
    volume       bigint,
    amount       numeric(24,2),
    change_rate  numeric(10,6)
);

numeric 会按照列定义的小数位进行舍入：

SELECT 12.3456::numeric(10, 2);

结果：

12.35

PostgreSQL 官方建议在货币金额等要求精确的场景中使用 numeric。数值类型文档

2. `real` 和 `double precision`

类型	精度	特点
`real`	约6位十进制数字	4字节浮点数
`double precision`	约15位十进制数字	8字节浮点数

temperature double precision

浮点数是近似值：

SELECT 0.1::double precision + 0.2::double precision;

因此不要用它保存必须精确相等的金额、价格。

选择原则：

金额、股价：numeric
科学计算、统计模型、允许误差的指标：double precision
通常不建议使用精度较低的 real

3. `money`

PostgreSQL 有专门的货币类型：

amount money

但它受数据库区域设置影响，货币符号、格式和精度不够灵活。业务系统通常更推荐：

amount numeric(18, 2)
currency_code varchar(3)

四、字符串类型

1. `text`

company_name text
description  text

text 可以保存任意长度的字符串，是 PostgreSQL 中最常用的字符串类型。

CREATE TABLE company (
    name            text NOT NULL,
    business        text,
    company_feature text
);

2. `varchar(n)`

code varchar(6)
name varchar(100)

超过指定长度会报错：

CREATE TABLE stock (
    code varchar(6)
);

varchar(n) 的主要价值是限制长度，并不意味着它一定比 text 查询更快。

如果业务没有明确长度限制，直接使用：

content text

3. `char(n)`

code char(6)

char(n) 是定长字符串，不足长度时会补充空格。它容易造成字符串比较、显示和程序处理方面的困惑。

股票代码更建议：

code varchar(6) NOT NULL

配合检查约束：

CONSTRAINT ck_stock_code
CHECK (code ~ '^[0-9]{6}$')

而不是：

code char(6)

4. `citext`

citext 是扩展提供的大小写不敏感字符串：

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE users (
    email citext UNIQUE
);

此时下面两个邮箱会被认为相同：

User@example.com
user@example.com

五、布尔类型

is_st boolean NOT NULL DEFAULT false

布尔值有三种逻辑状态：

TRUE
FALSE
NULL

查询：

SELECT *
FROM stock
WHERE is_st = false;

更简洁的写法：

SELECT *
FROM stock
WHERE NOT is_st;

判断可空布尔值时，可以使用：

WHERE is_st IS TRUE
WHERE is_st IS FALSE
WHERE is_st IS NULL

不要用字符串保存布尔状态：

-- 不推荐
is_st varchar(10)

六、日期与时间类型

类型	含义	示例
`date`	日期	`2026-09-14`
`time`	一天中的时间	`09:30:00`
`timestamp`	无时区日期时间	`2026-09-14 09:30:00`
`timestamptz`	有明确时间线意义的时间点	行情时间、创建时间
`interval`	时间长度	`3 days`

1. `date`

适合交易日、生日、报告日期：

trade_date date NOT NULL

SELECT CURRENT_DATE;

日期运算：

SELECT CURRENT_DATE + 7;
SELECT CURRENT_DATE - DATE '2026-09-01';

2. `timestamp`

完整名称：

timestamp without time zone

它只保存表面上的年月日时分秒，不代表全球时间线上的唯一时刻。

适合：

每天固定的营业时间
不考虑时区的本地计划时间
已经明确约定时区的内部数据

3. `timestamptz`

完整名称：

timestamp with time zone

推荐用于：

created_at
updated_at
登录时间
行情采集时间
日志时间
跨时区业务时间

created_at timestamptz NOT NULL DEFAULT now()

需要注意：timestamptz 保存的是一个确定的时间点，PostgreSQL 会根据当前会话时区显示它，但不会保留原始输入中的时区名称。

SET TIME ZONE 'Asia/Shanghai';

SELECT now();

建议：

数据库存储统一使用 timestamptz
服务器和程序内部尽量使用 UTC
展示时再转换为用户所在时区

SELECT created_at AT TIME ZONE 'Asia/Shanghai'
FROM users;

详见 PostgreSQL 日期与时间类型。

4. `interval`

表示时间长度：

SELECT now() - interval '7 days';
SELECT now() + interval '30 minutes';

查询最近7天数据：

SELECT *
FROM stock_tick
WHERE recorded_at >= now() - interval '7 days';

七、UUID

id uuid PRIMARY KEY

PostgreSQL 18 支持直接生成 UUID：

SELECT gen_random_uuid();
SELECT uuidv7();

建表：

CREATE TABLE orders (
    id uuid PRIMARY KEY DEFAULT uuidv7(),
    created_at timestamptz NOT NULL DEFAULT now()
);

PostgreSQL 18 新增的 uuidv7() 生成具有时间顺序特征的 UUID。与随机 UUIDv4 相比，作为 B-tree 主键时通常具有更好的插入局部性。

选择建议：

单机内部表：bigint identity
分布式系统、公开接口：uuid
PostgreSQL 18 新项目：可优先考虑 uuidv7()

PostgreSQL UUID 类型

八、JSON 与 JSONB

PostgreSQL 提供两种 JSON 类型：

类型	保存方式	查询和索引
`json`	保存原始文本	较弱
`jsonb`	解析后的二进制结构	更强

一般优先使用：

jsonb

CREATE TABLE company (
    code       varchar(6) PRIMARY KEY,
    attributes jsonb NOT NULL DEFAULT '{}'::jsonb
);

插入：

INSERT INTO company (code, attributes)
VALUES (
    '600519',
    '{
        "industry": "白酒",
        "concepts": ["国企", "消费"],
        "province": "贵州"
    }'
);

读取字段：

SELECT attributes->'concepts'
FROM company;

读取为文本：

SELECT attributes->>'industry'
FROM company;

条件查询：

SELECT *
FROM company
WHERE attributes @> '{"industry": "白酒"}';

建立 GIN 索引：

CREATE INDEX idx_company_attributes
ON company USING gin (attributes);

但不要把所有字段都塞进 jsonb：

经常查询、关联和排序的核心字段应使用普通列
结构不固定、低频使用的扩展信息适合 jsonb
jsonb 不能代替合理的关系模型

PostgreSQL JSON 类型

九、数组类型

PostgreSQL 可以直接保存数组：

concepts text[]

CREATE TABLE stock (
    code     varchar(6) PRIMARY KEY,
    concepts text[] NOT NULL DEFAULT '{}'
);

插入：

INSERT INTO stock (code, concepts)
VALUES ('600519', ARRAY['白酒', '国企', '消费']);

查询包含某个元素的记录：

SELECT *
FROM stock
WHERE '白酒' = ANY(concepts);

或者：

SELECT *
FROM stock
WHERE concepts @> ARRAY['白酒'];

建立索引：

CREATE INDEX idx_stock_concepts
ON stock USING gin (concepts);

数组适合：

标签
简单权限集合
不需要附加属性的小型列表

如果每个概念还包含名称、分类、来源、更新时间等属性，应设计关联表：

stock
concept
stock_concept

十、枚举类型

创建枚举：

CREATE TYPE market_type AS ENUM (
    '主板',
    '创业板',
    '科创板',
    '北交所'
);

使用：

CREATE TABLE stock (
    code   varchar(6) PRIMARY KEY,
    market market_type NOT NULL
);

优点：

类型安全
防止非法值
可读性好

缺点：

修改枚举结构不如普通表灵活
类型在多个表之间形成依赖
频繁变化的业务状态不适合枚举

稳定状态可以使用枚举；经常变化的板块、行业、分类更适合单独建立字典表。

十一、二进制类型

file_content bytea

适合保存：

二进制内容
加密结果
哈希值
小型文件

但大型图片、音频、视频通常更适合存放在文件系统或对象存储中，数据库只保存地址和元数据。

十二、网络地址类型

client_ip inet
network    cidr
mac        macaddr

示例：

CREATE TABLE login_log (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    bigint NOT NULL,
    client_ip  inet NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

查询某个网段：

SELECT *
FROM login_log
WHERE client_ip << '192.168.1.0/24'::cidr;

IP 地址不要简单保存成 varchar，使用 inet 能获得格式校验、网络运算和更合理的索引能力。

十三、范围类型

PostgreSQL 可以保存一个区间：

类型	内容
`int4range`	`integer` 范围
`int8range`	`bigint` 范围
`numrange`	`numeric` 范围
`daterange`	日期范围
`tsrange`	无时区时间范围
`tstzrange`	带时区时间范围

CREATE TABLE promotion (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    valid_period daterange NOT NULL
);

插入：

INSERT INTO promotion (valid_period)
VALUES ('[2026-09-01, 2026-10-01)');

其中：

[：包含起点
]：包含终点
(：不包含起点
)：不包含终点

判断日期是否在范围内：

SELECT *
FROM promotion
WHERE DATE '2026-09-14' <@ valid_period;

适合有效期、价格区间、预约时间等业务。

十四、类型转换

显式转换有两种写法。

标准 SQL：

SELECT CAST('123' AS integer);

PostgreSQL 简写：

SELECT '123'::integer;

常见转换：

SELECT '2026-09-14'::date;
SELECT '12.35'::numeric(10,2);
SELECT '{"name":"贵州茅台"}'::jsonb;
SELECT '192.168.1.1'::inet;

查看表达式类型：

SELECT pg_typeof(123);
SELECT pg_typeof(123::bigint);
SELECT pg_typeof(now());

不要依赖复杂场景下的隐式转换，尤其是：

字符串与数字比较
日期字符串转换
jsonb 字段与普通列比较
联表字段类型不一致

十五、股票数据的推荐设计

CREATE TABLE stock_daily (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code         varchar(6)    NOT NULL,
    trade_date   date          NOT NULL,

    open_price   numeric(18,4),
    high_price   numeric(18,4),
    low_price    numeric(18,4),
    close_price  numeric(18,4),
    pre_close    numeric(18,4),

    volume       bigint,
    amount       numeric(24,2),
    change_rate  numeric(10,6),
    turnover     numeric(10,6),

    is_suspended boolean       NOT NULL DEFAULT false,
    raw_data     jsonb,
    created_at   timestamptz   NOT NULL DEFAULT now(),

    CONSTRAINT uq_stock_daily
        UNIQUE (code, trade_date),

    CONSTRAINT ck_stock_code
        CHECK (code ~ '^[0-9]{6}$'),

    CONSTRAINT ck_stock_price
        CHECK (
            open_price >= 0
            AND high_price >= 0
            AND low_price >= 0
            AND close_price >= 0
        )
);

其中最重要的选择是：

股票代码用 varchar(6)，不能用整数，否则会丢失前导零
股价和金额用 numeric
成交量用 bigint
交易日期用 date
采集时间用 timestamptz
原始接口数据用 jsonb
是否停牌用 boolean

一句话总结：

类型选择的核心不是“能不能存进去”，而是让数据库准确表达数据的业务含义，并主动阻止错误数据进入。