PostgreSQL Data Types 数据类型实用教程
本教程介绍 PostgreSQL 常见内置类型与扩展类型。SQL 示例可按需在测试数据库中执行；创建类型的示例应先确认名称没有冲突。
数据类型速览
| 类别 | 常见类型 | 典型用途 |
|---|---|---|
| 数值（Numeric） | smallint、integer、bigint、numeric、real、double precision | 整数、精确小数、科学计算 |
| 字符（Character） | text、varchar、char | 文本与有长度限制的字符串 |
| 二进制（Binary） | bytea | 小型二进制内容 |
| 日期/时间（Date/Time） | date、time、timestamp、timestamptz、interval | 日期、时刻、时间点与时长 |
| 布尔（Boolean） | boolean | 真、假与未知 |
| 枚举（Enumerated） | 用户定义的 ENUM | 有限且相对稳定的取值集合 |
| 网络地址（Network address） | inet、cidr、macaddr | IP 地址、网段与 MAC 地址 |
| 位串（Bit string） | bit、bit varying | 位掩码与按位数据 |
| 全文搜索（Text Search） | tsvector、tsquery | 文档索引与搜索条件 |
| 标识符与结构化数据 | uuid、json、jsonb、数组、Range、Domain | 全局 ID、灵活结构、集合、范围与受约束类型 |
| 对象标识符（Object identifier） | oid、regclass 等 | 数据库系统目录对象引用 |
Numeric：数值类型
整数类型按范围选择；numeric 提供精确十进制计算；real 和 double precision 是近似浮点类型。
| 类型 | 说明 | 适用场景 |
|---|---|---|
smallint | 2 字节有符号整数 | 范围较小的计数或编码 |
integer / int | 4 字节有符号整数 | 常见整数量值 |
bigint | 8 字节有符号整数 | 较大计数与整数 ID |
numeric(p, s) / decimal | 精确十进制；p 为精度，s 为小数位数 | 金额、需要精确十进制的数 |
real / double precision | 近似浮点数，后者精度更高 | 科学计算、可接受近似误差的计算 |
CREATE TABLE measurements ( item_count integer NOT NULL, price numeric(12, 2) NOT NULL, ratio double precision );
SELECT 10 / 3;
-- 整数除法：3
SELECT 10::numeric / 3;
-- 精确十进制运算
金额通常用 numeric，避免二进制浮点近似误差。serial、bigserial 是历史上常用的自增列写法，不是独立的真正数据类型；新设计也可使用 identity 列。
Character：字符类型
text 和 varchar 在 PostgreSQL 中性能相近。只有确实需要限制最大字符数时才指定 varchar(n)；char(n) 会按固定长度语义补空格。
CREATE TABLE profiles ( display_name varchar(80) NOT NULL, biography text, country_code char(2) );
SELECT 'hello'::text;
PostgreSQL 的字符长度按字符而非字节计算。char(n) 比较时尾随空格的处理容易令人意外，通常优先选 text 或 varchar。若要限制内容，应同时考虑空字符串、空格以及业务格式。
Binary Data：二进制数据
bytea 用于存储字节序列。PostgreSQL 常以十六进制格式显示二进制值；decode 和 encode 可在字节与文本表示之间转换。
CREATE TABLE attachments ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, content bytea NOT NULL );
-- 把十六进制文本转换为 bytea
SELECT decode('DEADBEEF', 'hex');
-- bytea 转为十六进制文本
SELECT encode(decode('DEADBEEF', 'hex'), 'hex');
大型文件通常更适合存储在对象存储或文件系统中，数据库只保存地址、校验值和元数据；是否使用 bytea 要结合备份、传输和访问模式决定。
Date/Time：日期与时间
| 类型 | 含义 | 提示 |
|---|---|---|
date | 日历日期 | 不包含时刻和时区 |
time / time with time zone | 一天中的时刻 | 带时区的 time 在业务中较少使用 |
timestamp | 不带时区的日期和时间字段 | 本身不代表唯一的全球时间点 |
timestamptz | 带时区语义的时间点 | 内部按时间点处理，输入和显示受时区设置影响 |
interval | 时间间隔 | 可表示月、日和时间部分 |
CREATE TABLE appointments ( appointment_date date NOT NULL, starts_at timestamptz NOT NULL, duration interval NOT NULL DEFAULT interval '30 minutes' );
SELECT date '2026-10-20';
SELECT timestamptz '2026-10-20 09:30:00+08';
SELECT now() + interval '1 day';
保存实际发生的时刻通常选 timestamptz；营业时间等纯本地钟表时间可选 time。采用 ISO 日期格式，并在应用中明确时区，避免依赖会话默认值。
Boolean：布尔类型
boolean 有 true、false 和 NULL（未知）三种逻辑状态。
CREATE TABLE feature_flags ( enabled boolean NOT NULL DEFAULT false );
SELECT true, false;
SELECT * FROM feature_flags WHERE enabled IS TRUE;
如果业务必须明确回答是或否，使用 NOT NULL；如果 NULL 表示“未知”或“尚未设置”，则需要在查询时显式处理三值逻辑。
Enumerated：枚举类型
枚举用于有限、相对稳定且有明确顺序的值集合。枚举标签区分大小写，排序顺序由创建类型时声明的顺序决定。
CREATE TYPE order_status AS ENUM ( 'pending', 'paid', 'shipped', 'cancelled' );
CREATE TABLE orders ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, status order_status NOT NULL DEFAULT 'pending' );
ALTER TYPE order_status ADD VALUE 'refunded';
枚举值不适合频繁变化或由用户动态管理的类别。可以添加新值，但移除或重排已有值通常需要迁移并重建类型；可变分类常用独立字典表和外键表达。
Network Address：网络地址类型
inet 表示 IPv4 或 IPv6 主机地址/网络，cidr 表示网络地址块，macaddr 和 macaddr8 表示硬件地址。
CREATE TABLE access_logs ( client_ip inet NOT NULL, allowed_network cidr );
SELECT inet '192.168.1.25';
SELECT cidr '192.168.1.0/24';
SELECT inet '192.168.1.25' << cidr '192.168.1.0/24';
inet 可保存带掩码的地址；如果输入必须是网络地址并自动清除主机位，用 cidr。网络类型支持专用运算符和函数，通常比把 IP 存成普通文本更便于验证与网段查询。
Bit String：位串类型
bit(n) 是固定长度位串；bit varying(n)（也写作 varbit(n)）是最大长度受限的可变长度位串。
CREATE TABLE masks ( fixed_mask bit(8), variable_mask varbit(32) );
SELECT B'10110010'::bit(8);
SELECT B'1010' & B'1100';
-- 按位 AND
位串长度、补齐和运算规则与整数不同。只有数据本身确实是位序列或需要按位操作时才使用；普通开关集合也可考虑布尔列或整数位掩码，按查询需求选型。
Text Search：全文搜索类型
tsvector 是经过分词和归一化的文档表示；tsquery 是搜索条件。文本搜索配置会影响语言分词、词干化和停用词处理。
SELECT to_tsvector('english', 'The quick brown fox');
SELECT plainto_tsquery('english', 'quick fox');
SELECT to_tsvector('english', 'The quick brown fox') @@ plainto_tsquery('english', 'quick fox');
常用做法是为文本建立生成的 tsvector 列并创建 GIN 索引。语言配置需与内容语言匹配；混合语言或中文搜索通常需要专门配置或扩展。
CREATE TABLE articles ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, body text NOT NULL, search_vector tsvector GENERATED ALWAYS AS ( to_tsvector('english', body) ) STORED );
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);
UUID：通用唯一标识符
uuid 保存 128 位标识符，常用于分布式生成或不希望公开连续整数 ID 的场景。PostgreSQL 支持 UUID 字面值和专用列类型。
CREATE TABLE api_clients ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name text NOT NULL );
INSERT INTO api_clients (name) VALUES ('example') RETURNING id;
gen_random_uuid() 在现代 PostgreSQL 版本中可用；旧版本环境请确认版本和所需扩展。随机 UUID 会增加索引写入的随机性；若写入量很大，结合版本、索引局部性和分布式需求评估 UUID 版本及主键设计。
JSON：JSON 与 JSONB
json 保存输入文本并在读取时解析；jsonb 以二进制形式存储，适合查询、比较和索引，但不会保留空白、对象键顺序或重复键。
CREATE TABLE events ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, payload jsonb NOT NULL );
INSERT INTO events (payload) VALUES ('{"type":"signup","user":{"id":42}}'::jsonb);
SELECT payload -> 'user' AS user_object, payload ->> 'type' AS event_type FROM events WHERE payload @> '{"type":"signup"}'::jsonb;
CREATE INDEX idx_events_payload ON events USING GIN (payload);
-> 返回 JSON 值，->> 返回文本；@> 检查包含关系。经常过滤、关联或约束的字段应优先考虑独立的类型化列；GIN 索引要结合查询运算符和索引操作类选择。
ARRAY：数组类型
PostgreSQL 可以为任意内置或用户定义类型创建数组。常见写法是 类型[]；数组下标默认从 1 开始，声明维度并不强制数组实际维度或大小。
CREATE TABLE surveys ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, tags text[] NOT NULL DEFAULT '{}' );
INSERT INTO surveys (tags) VALUES (ARRAY['postgres', 'database']);
SELECT tags[1], cardinality(tags) FROM surveys;
SELECT * FROM surveys WHERE tags @> ARRAY['postgres'];
数组适合简单、有限的集合属性，但不适合替代需要独立查询、约束或关联的子表。数组可以包含 NULL，数组比较与包含运算也要注意 NULL 语义。
Range：范围类型
范围类型表示一段连续区间，常用于日期区间、数字区间和排期。边界可为包含 [/] 或不包含 (/)；常见惯例是左闭右开 [)。
| 范围类型 | 基类型 | 典型用途 |
|---|---|---|
int4range | integer | 整数范围 |
int8range | bigint | 大整数范围 |
numrange | numeric | 精确数值范围 |
tsrange / tstzrange | timestamp / timestamptz | 本地时间或时间点区间 |
daterange | date | 日期区间 |
SELECT daterange(date '2026-10-01', date '2026-11-01', '[)');
SELECT daterange(date '2026-10-01', date '2026-11-01', '[)') @> date '2026-10-20';
-- 排除重叠预订；需要 btree_gist 扩展支持标量等值部分
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE reservations ( room_id integer NOT NULL, during tstzrange NOT NULL, EXCLUDE USING gist (room_id WITH =, during WITH &&) );
范围运算符包括包含判断 @>、重叠判断 && 和交集等。PostgreSQL 14 及更高版本还支持对应的多范围类型，用于表示多个不连续区间。
Domain：域类型
Domain 是基于已有类型并附加约束的用户定义类型，可集中复用格式或取值规则。Domain 仍保留基础类型的大多数操作和行为。
CREATE DOMAIN positive_price AS numeric(12, 2) CHECK (VALUE >= 0);
CREATE TABLE products ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, price positive_price NOT NULL );
CHECK 约束通常不会因表达式结果为 NULL 而判定失败，因此当 Domain 的值不允许为空时，应同时用列级 NOT NULL，或在 Domain 约束中明确拒绝 NULL。
CREATE DOMAIN required_code AS text NOT NULL CHECK (length(VALUE) > 0);
Object Identifier：对象标识符类型
oid 是 PostgreSQL 内部使用的对象标识符类型。regclass、regtype、regprocedure 等是便于按名称解析特定系统对象的 OID 别名类型。
-- regclass 可将表名解析为系统目录中的关系对象标识
SELECT 'pg_class'::regclass;
-- 查看当前 schema 中可 见的表及其对象标识
SELECT c.oid, c.oid::regclass AS relation_name FROM pg_class AS c WHERE c.relkind = 'r' ORDER BY c.oid LIMIT 10;
OID 主要服务于系统目录和 PostgreSQL 内部对象引用，不应当作业务主键或永久、跨数据库稳定的 ID。导出、重建、恢复或对象变化都可能使其失去原有含义；业务实体请使用合适的整数或 UUID 键。
类型选择建议
- 优先选择语义准确、范围足够且应用和数据库都能明确处理的类型。
- 金额用
numeric；近似科学计算再考虑浮点数。 - 文本默认考虑
text；只有确有长度规则时使用varchar(n)或char(n)。 - 表示时间点用
timestamptz，表示不带时区的本地时间字段用timestamp。 - 经常参与过滤、连接或约束的 JSON 字段，评估是否应建成独立列。
- 对 Domain、Enum、Range 等高级类型，确认驱动程序、ORM、迁移工具及运维工具均支持。
- 实际查询性能用代表性数据和
EXPLAIN验证，不要仅凭类型名称推断。