PostgreSQL 数据类型

数值类型

整型

数据类型	存储大小	范围
SMALLINT	2 字节	-32768 到 32767
INTEGER	4 字节	-2147483648 到 2147483647
BIGINT	8 字节	-9223372036854775808 到 9223372036854775807
SMALLSERIAL	2 字节	1 到 32767
SERIAL	4 字节	1 到 2147483647
BIGSERIAL	8 字节	1 到 9223372036854775807

SERIAL 类型是自增整型的简写，PostgreSQL 会自动创建序列（SEQUENCE）。

浮点型

数据类型	存储大小	精度	范围
REAL	4 字节	6 位十进制精度	1E-37 到 1E+37
DOUBLE PRECISION	8 字节	15 位十进制精度	1E-307 到 1E+308
NUMERIC	可变	用户指定精度	无限制

NUMERIC 类型可以指定精度和小数位数：

NUMERIC(precision, scale)
-- 例如：NUMERIC(10, 2) 表示总共 10 位数字，小数点后 2 位

NUMERIC 类型用于存储精确的数值，不会出现浮点数精度丢失问题，适合存储货币等需要精确计算的数值。

字符类型

数据类型	说明
CHAR(n)	固定长度字符串，不足部分用空格填充，最大长度 10485760
VARCHAR(n)	可变长度字符串，最大长度 10485760
TEXT	可变长度字符串，无长度限制
CHARACTER(n)	CHAR(n) 的别名
CHARACTER VARYING(n)	VARCHAR(n) 的别名

使用建议

• 如果字符串长度固定，使用 CHAR(n)
• 如果字符串长度可变但有限制，使用 VARCHAR(n)
• 如果字符串长度不确定或可能很长，使用 TEXT
• VARCHAR 不指定长度时等同于 TEXT

日期时间类型

数据类型	存储大小	说明	范围
TIMESTAMP	8 字节	日期和时间（无时区）	4713 BC 到 294276 AD
TIMESTAMPTZ	8 字节	日期和时间（带时区）	4713 BC 到 294276 AD
DATE	4 字节	日期	4713 BC 到 5874897 AD
TIME	8 字节	时间（无时区）	00:00:00 到 24:00:00
TIMETZ	12 字节	时间（带时区）	00:00:00+1559 到 24:00:00-1559
INTERVAL	16 字节	时间间隔	-178000000 年到 178000000 年

时区处理

TIMESTAMPTZ 存储时会转换为 UTC 时间，查询时会根据当前会话的时区设置转换回本地时间。

设置会话时区：

SET timezone = 'Asia/Shanghai';
-- 或
SET timezone = 'UTC';

查看当前时区：

SHOW timezone;

布尔类型

数据类型	存储大小	说明
BOOLEAN	1 字节	真值或假值

布尔值可以使用以下表示：

• TRUE / true / t / yes / y / on / 1
• FALSE / false / f / no / n / off / 0
• NULL

数组类型

PostgreSQL 支持数组类型，任何数据类型都可以创建对应的数组类型。

创建数组列：

CREATE TABLE example (
    id INTEGER,
    tags TEXT[],
    scores INTEGER[]
);

插入数组数据：

INSERT INTO example VALUES (1, ARRAY['tag1', 'tag2'], ARRAY[90, 85, 92]);
-- 或
INSERT INTO example VALUES (1, '{tag1, tag2}', '{90, 85, 92}');

查询数组：

-- 访问数组元素（索引从 1 开始）
SELECT tags[1] FROM example;

-- 检查数组是否包含某个值
SELECT * FROM example WHERE 'tag1' = ANY(tags);

-- 数组长度
SELECT array_length(tags, 1) FROM example;

JSON 类型

PostgreSQL 提供了两种 JSON 数据类型：

数据类型	说明
JSON	存储 JSON 数据，不验证格式
JSONB	存储 JSON 数据，验证格式并以二进制格式存储

JSONB 类型：

• 会验证 JSON 格式
• 以二进制格式存储，查询性能更好
• 支持索引（GIN 索引）
• 会删除重复的键，保留最后一个值
• 会删除对象键的顺序

创建 JSON 列：

CREATE TABLE example (
    id INTEGER,
    data JSONB
);

插入 JSON 数据：

INSERT INTO example VALUES (1, '{"name": "张三", "age": 30}');

查询 JSON 数据：

-- 访问 JSON 字段
SELECT data->>'name' FROM example;
SELECT data->'age' FROM example;

-- 使用 JSON 操作符
SELECT * FROM example WHERE data->>'name' = '张三';
SELECT * FROM example WHERE data @> '{"age": 30}';

UUID 类型

UUID 类型用于存储通用唯一标识符（UUID），存储大小为 16 字节。

使用前需要启用 uuid-ossp 扩展：

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

生成 UUID：

-- 使用函数生成
SELECT uuid_generate_v4();

-- 创建表时使用默认值
CREATE TABLE example (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name VARCHAR(100)
);

网络地址类型

PostgreSQL 提供了专门的数据类型来存储网络地址：

数据类型	存储大小	说明
INET	7 或 19 字节	IPv4 或 IPv6 地址
CIDR	7 或 19 字节	IPv4 或 IPv6 网络地址
MACADDR	6 字节	MAC 地址
MACADDR8	8 字节	EUI-64 格式 MAC 地址

示例：

CREATE TABLE network_devices (
    id SERIAL PRIMARY KEY,
    ip_address INET,
    network CIDR,
    mac_address MACADDR
);

INSERT INTO network_devices VALUES 
    (1, '192.168.1.100', '192.168.1.0/24', '08:00:2b:01:02:03');

几何类型

PostgreSQL 通过 PostGIS 扩展支持几何数据类型：

数据类型	说明
POINT	点
LINE	直线
LSEG	线段
BOX	矩形
PATH	路径
POLYGON	多边形
CIRCLE	圆

使用前需要启用 PostGIS 扩展：

CREATE EXTENSION IF NOT EXISTS postgis;

范围类型

PostgreSQL 支持范围类型，用于表示一个值的范围：

数据类型	说明
INT4RANGE	整数范围
INT8RANGE	大整数范围
NUMRANGE	数值范围
TSRANGE	时间戳范围
TSTZRANGE	带时区时间戳范围
DATERANGE	日期范围

示例：

CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER,
    period TSRANGE
);

INSERT INTO reservations VALUES 
    (1, 101, '[2024-01-01 10:00:00, 2024-01-01 12:00:00)');

查询范围：

-- 检查范围是否包含某个值
SELECT * FROM reservations WHERE period @> '2024-01-01 11:00:00'::timestamp;

-- 检查范围是否重叠
SELECT * FROM reservations WHERE period && '[2024-01-01 11:00:00, 2024-01-01 13:00:00)'::tsrange;

二进制类型

数据类型	存储大小	说明
BYTEA	可变	二进制数据

BYTEA 类型用于存储二进制数据，如图片、文件等。

示例：

CREATE TABLE files (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    content BYTEA
);

文本搜索类型

PostgreSQL 提供了专门的文本搜索类型：

数据类型	说明
TSVECTOR	文本搜索向量
TSQUERY	文本搜索查询

用于全文搜索功能。

自定义类型

PostgreSQL 允许创建自定义数据类型：

-- 创建枚举类型
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    current_mood mood
);

-- 创建复合类型
CREATE TYPE address AS (
    street VARCHAR(100),
    city VARCHAR(50),
    zip_code VARCHAR(10)
);

CREATE TABLE customer (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    addr address
);

类型转换

PostgreSQL 提供了类型转换功能：

-- 使用 CAST
SELECT CAST('123' AS INTEGER);

-- 使用 :: 操作符
SELECT '123'::INTEGER;

-- 函数形式
SELECT '123'::INTEGER::TEXT;

参考

• PostgreSQL 官方文档 - 数据类型