PostgreSQL 数据类型

PostgreSQL 拥有开源数据库里最丰富的类型系统:除基本的数值/文本/时间外,还有数组、JSON/JSONB、枚举、UUID、范围类型、几何类型,甚至自定义类型。理解了类型,你设计表结构时就能"对症下药",既不浪费空间,又能让数据库帮你校验数据正确性。

1. 数值类型

数值类型分三大类:整数、浮点、精确小数。关键区别在精度——存钱款绝不能用浮点。

-- 整数类型(按存储宽度分)
--   smallint / int2 : 2 字节,-32768 ~ 32767
--   integer  / int4 : 4 字节,-21亿 ~ 21亿(最常用)
--   bigint   / int8 : 8 字节,±9.2e18
--   serial         : 自增 int(实际是 int + sequence)
--   bigserial      : 自增 bigint
CREATE TABLE nums (
    id        SERIAL PRIMARY KEY,
    count     INTEGER DEFAULT 0,
    big_count BIGINT
);

-- 浮点类型(近似值,有精度损失)
--   real       / float4 : 4 字节,6 位有效数字
--   double precision / float8 : 8 字节,15 位有效数字
CREATE TABLE floats (
    pi REAL DEFAULT 3.14159,
    precise DOUBLE PRECISION DEFAULT 3.141592653589793
);

-- 精确小数 NUMERIC(存钱、利率、计费的必备)
--   NUMERIC(precision, scale)
--   precision = 总位数, scale = 小数位数
--   不写 precision/scale 则任意精度
CREATE TABLE money (
    price NUMERIC(10, 2) NOT NULL,    -- 最大 99999999.99
    rate  NUMERIC(5, 4) DEFAULT 0.0000 -- 0.0000 ~ 9.9999
);

-- NUMERIC 不会丢精度:
SELECT 0.1::NUMERIC + 0.2::NUMERIC;   -- 0.3(精确)
SELECT 0.1::REAL    + 0.2::REAL;      -- 0.30000001(近似)

三条选型经验:

• 整数:小项目用 SERIAL(自增 int),大型业务表用 BIGSERIAL(可能超 21 亿行)。
• 科学计算:用 DOUBLE PRECISION,15 位有效数字。
• 钱、利率、计费:必须用 NUMERIC,绝不用浮点。浮点的 0.1 + 0.2 不等于 0.3 是经典坑。

2. 字符类型

Postgres 的字符类型有三种,但日常只用 TEXT。这跟 MySQL 不同(MySQL 推荐 VARCHAR)。

-- 字符类型:三种,但日常只用 TEXT
--   char(n)   / character(n)     : 定长,不足补空格(几乎不用)
--   varchar(n)/ character varying(n) : 变长,有上限
--   text                          : 变长,无上限(Postgres 推荐)
CREATE TABLE posts (
    title  VARCHAR(200),   -- 最多 200 字符
    body   TEXT            -- 想多长多长
);

-- Postgres 中 varchar 和 text 在底层存储完全一样,
-- varchar(n) 的长度限制只是约束,不影响性能。
-- 社区约定:能用 text 就用 text,除非需要明确长度约束。

-- 字符串常用操作
SELECT length('你好');              -- 2(字符数)
SELECT char_length('你好');          -- 2
SELECT octet_length('你好');         -- 6(UTF-8 字节数)
SELECT upper('hello');              -- HELLO
SELECT lower('HELLO');              -- hello
SELECT substring('hello' from 2 for 3);  -- ell
SELECT 'a' || 'b';                  -- ab(字符串拼接)
SELECT concat('a', 'b', 'c');       -- abc

核心要点:在 Postgres 里 VARCHAR 和 TEXT 底层存储完全一样,VARCHAR(n) 的 n 只是一个长度约束。社区约定能用 TEXT 就用 TEXT,需要明确限制长度时再用 VARCHAR。

3. 时间类型(初学者最大坑)

时间类型看着多,其实就两个核心:带时区和不带时区。选错了未来调试"差 8 小时"会让你怀疑人生。

-- 日期 / 时间类型(初学最容易混)
--   date              : 仅日期(4 字节)
--   time              : 仅时间(无时区)
--   timetz            : 时间 + 时区
--   timestamp         : 日期 + 时间(无时区,8 字节)
--   timestamptz       : 日期 + 时间 + 时区(8 字节,强烈推荐!)
--   interval          : 时间间隔(如 '1 day 2 hours')
CREATE TABLE events (
    id         SERIAL PRIMARY KEY,
    d          DATE DEFAULT CURRENT_DATE,         -- 仅日期
    t          TIME DEFAULT CURRENT_TIME,         -- 仅时间
    created_at TIMESTAMPTZ DEFAULT now(),         -- 完整时间戳(带时区)
    duration   INTERVAL DEFAULT '0 seconds'       -- 时间间隔
);

-- 重要!带时区 vs 不带时区
--   timestamptz 内部统一存 UTC,展示时按客户端时区转换
--   timestamp 不存时区信息,"看到什么就是什么"
-- 跨时区项目一律用 timestamptz,避免"差 8 小时"的怪事

-- 当前时间函数
SELECT now();                       -- 2026-08-05 14:30:00.123456+08
SELECT CURRENT_TIMESTAMP;           -- 同 now()
SELECT CURRENT_DATE;                -- 2026-08-05
SELECT CURRENT_TIME;                -- 14:30:00.123456+08

-- 时间运算(interval 是神器)
SELECT now() + interval '1 day';         -- 明天此刻
SELECT now() - interval '2 hours';       -- 2 小时前
SELECT now() AT TIME ZONE 'UTC';         -- 转为 UTC 时间
SELECT date_trunc('month', now());       -- 本月第一天 00:00
SELECT date_part('year', now());         -- 2026(返回 double)
SELECT EXTRACT(MONTH FROM now());        -- 8(标准 SQL 写法)

一句话原则:跨时区项目一律用 TIMESTAMPTZ。它内部存 UTC,展示时按客户端时区转换,杜绝时差问题。TIMESTAMP(不带时区)只在"看到什么就是什么"的本地场景用,比如生日(不论时区)。

interval 类型用来表示时间间隔(如 1 day 2 hours),做时间运算时极其方便。date_trunc 把时间截断到指定精度,做日/周/月报表必备。

4. 布尔类型

Postgres 的 BOOLEAN 是严格三态:true / false / null。注意它不接受 0/1 作为布尔值(不像 MySQL),必须用 true / false 或 t / f。

5. UUID — 全局唯一标识符

当 SERIAL(自增整数)不够用时——比如多个数据库合并、不想暴露行数、需要客户端预生成 ID——UUID 派上用场。它是 128 位的随机数,碰撞概率小到可以忽略。

-- 1. 布尔类型:strict 三态(true/false/null)
CREATE TABLE todos (
    id     SERIAL PRIMARY KEY,
    done   BOOLEAN DEFAULT FALSE
);
SELECT true, false, null::boolean;

-- 2. UUID(全局唯一标识符,128 位)
-- 需先装扩展生成 UUID
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE sessions (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id INT NOT NULL
);
-- 也可以用 pgcrypto 扩展的 gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT gen_random_uuid();   -- 如 550e8400-e29b-41d4-a716-446655440000

-- 3. 枚举类型(状态机、固定取值)
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE persons (
    id   SERIAL PRIMARY KEY,
    name TEXT,
    cur  mood DEFAULT 'ok'
);
INSERT INTO persons (name, cur) VALUES ('小明', 'happy');

-- 4. 数组类型(Postgres 特色,任意类型都可加 [])
CREATE TABLE posts_tags (
    id    SERIAL PRIMARY KEY,
    title TEXT,
    tags  TEXT[] DEFAULT '{}'    -- 文本数组,默认空数组
);
INSERT INTO posts_tags (title, tags) VALUES
    ('Postgres 入门', ARRAY['db', 'tutorial']),
    ('Go 教程', ARRAY['go', 'tutorial']);
SELECT title FROM posts_tags WHERE 'db' = ANY(tags);   -- 数组包含查询

-- 5. 范围类型(连续区间,适合预约/排期)
CREATE TABLE bookings (
    id      SERIAL PRIMARY KEY,
    room    INT,
    during  TSTZRANGE              -- 时间段范围
);
INSERT INTO bookings (room, during) VALUES
    (101, tstzrange('2026-08-05 14:00+08', '2026-08-05 15:00+08'));
-- 查找与某时间段重叠的预约
SELECT * FROM bookings
WHERE during && tstzrange('2026-08-05 14:30+08', '2026-08-05 15:30+08');

Postgres 的 UUID 类型是原生 16 字节存储,比 VARCHAR(36) 节省一半空间。生成 UUID 推荐用 pgcrypto 扩展的 gen_random_uuid()(Postgres 13+ 内置,无需扩展)。

6. 枚举类型 ENUM

状态机、固定取值(订单状态、用户等级)用 ENUM 比 CHECK 约束更优雅:类型本身限制了取值范围,且能在多处复用。

枚举的坑:枚举值是只读的,删除/重命名一个值需要特殊语法(ALTER TYPE ... DROP VALUE 在 Postgres 12 之前不支持)。如果你的取值集合会经常变,用查找表(lookup table)比 ENUM 更合适。

7. 数组类型(Postgres 特色)

任意类型都可以加 [] 变成数组。这能避免很多场景下"为了存几个标签再建一张关联表"的麻烦。比如文章的标签、用户的角色列表,用 TEXT[] 一列搞定。

• 写入:用 ARRAY[1,2,3] 构造器或字面量字符串 {1,2,3}。
• 查询包含:用 ANY() 或 @>。
• 索引:数组的等值查询可以建 GIN 索引。

但数组不是万能——如果数组元素需要单独查询、统计、JOIN,正规化为关联表更合适。

8. JSON / JSONB(本系列重点)

这是 PostgreSQL 区别于 MySQL 最闪光的特性,我们会用整一章(JSONB 章节)来讲它。这里先记住:JSONB 让你像 MongoDB 一样存灵活文档,又能享受 SQL 的全部能力,而且支持 GIN 索引让查询飞快。

9. 其他特色类型

• 范围类型(range):表示连续区间,如 INT4RANGE(整数范围)、TSTZRANGE(时间范围)。做会议室预约、排期冲突检测时极有用,内置 &&(重叠)、@>(包含)等运算符。
• 几何类型:点(point)、线(line)、多边形(polygon)、路径(path)、圆(circle)。配合 PostGIS 扩展能做完整 GIS。
• 网络地址类型:CIDR、INET、MACADDR,自动校验 IP 格式。
• 位串 BIT(n):固定位数的 0/1 串。
• hstore(键值对):扩展提供,JSONB 出现前很流行,现在大多用 JSONB 替代。

小结

这一章你认识了 PostgreSQL 的类型大全。核心记忆:整数/浮点/NUMERIC 选对精度;TEXT 优先于 VARCHAR;跨时区一律 TIMESTAMPTZ;UUID/数组/JSONB 是 Postgres 的三大特色类型。下一篇我们看 DDL——把这些类型组装成完整的表结构。

← 上一篇 PostgreSQL SQL 语法基础

下一篇 PostgreSQL DDL →