PostgreSQL 数据类型详解

yzqdev2026/7/13大约 4 分钟

PostgreSQL 数据类型详解

PostgreSQL 以丰富的数据类型著称，特别是对 JSON、数组和自定义类型的支持。

数字类型

类型名	占用	范围	说明
`SMALLINT` / `INT2`	2 字节	-32768 ~ 32767	小整数
`INTEGER` / `INT` / `INT4`	4 字节	-21亿 ~ 21亿	常用整数
`BIGINT` / `INT8`	8 字节	-922亿亿 ~ 922亿亿	大整数
`DECIMAL(p,s)` / `NUMERIC(p,s)`	可变	精度可达 131072 位	精确小数（推荐金额使用）
`REAL` / `FLOAT4`	4 字节	6 位十进制精度	浮点数
`DOUBLE PRECISION` / `FLOAT8`	8 字节	15 位十进制精度	双精度浮点
`SMALLSERIAL`	2 字节	1 ~ 32767	自增小整数
`SERIAL`	4 字节	1 ~ 21亿	自增整数
`BIGSERIAL`	8 字节	1 ~ 922亿亿	自增大整数

提示

PostgreSQL 推荐使用 GENERATED AS IDENTITY 替代 SERIAL（SQL 标准语法）：

-- 推荐（SQL 标准）
CREATE TABLE users (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(50)
);

-- 兼容方式
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

字符串类型

类型名	说明
`CHAR(n)`	定长字符串，不足补空格
`VARCHAR(n)`	变长字符串，有长度限制
`TEXT`	变长字符串，无长度限制（最多 1GB）

CREATE TABLE posts (
    title VARCHAR(200) NOT NULL,
    summary VARCHAR(500),
    content TEXT,
    code CHAR(10)  -- 固定长度
);

日期/时间类型

类型名	说明	示例
`DATE`	日期（年月日）	`2024-01-15`
`TIME`	时间（时分秒）	`14:30:25`
`TIMESTAMP`	日期+时间（无时区）	`2024-01-15 14:30:25`
`TIMESTAMPTZ`	日期+时间（带时区）	`2024-01-15 06:30:25+00`
`INTERVAL`	时间间隔	`1 year 2 months`

TIMESTAMP vs TIMESTAMPTZ

-- TIMESTAMP — 不关心时区，存什么就返回什么
CREATE TABLE events (
    event_name TEXT,
    event_time TIMESTAMP       -- 对应 Java LocalDateTime
);

-- TIMESTAMPTZ — 带时区，统一存储为 UTC
CREATE TABLE events_tz (
    event_name TEXT,
    event_time TIMESTAMPTZ     -- 对应 Java Instant
);

-- 使用
INSERT INTO events VALUES ('会议', '2024-01-15 14:30:00');
INSERT INTO events_tz VALUES ('会议', '2024-01-15 14:30:00+08');
-- 查询时会自动转换为客户端时区

布尔类型

CREATE TABLE tasks (
    title TEXT,
    is_done BOOLEAN DEFAULT FALSE
);

INSERT INTO tasks VALUES ('学习', TRUE);
INSERT INTO tasks VALUES ('运动', 'yes');    -- ✅ 可以写 'yes'/'no'
INSERT INTO tasks VALUES ('阅读', 't');      -- ✅ 可以写 't'/'f'
INSERT INTO tasks VALUES ('购物', '1');      -- ✅ 可以写 '1'/'0'

-- 查询
SELECT * FROM tasks WHERE is_done;
SELECT * FROM tasks WHERE is_done = TRUE;
SELECT * FROM tasks WHERE is_done = 't';

枚举类型

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

-- 使用
CREATE TABLE person (
    name TEXT,
    current_mood mood
);

INSERT INTO person VALUES ('张三', 'happy');
-- INSERT INTO person VALUES ('李四', 'unknown');  -- ❌ 不在枚举中

JSON / JSONB

PostgreSQL 对 JSON 的支持远超其他关系型数据库。

-- JSON — 存储 JSON 文本（保留格式、重复键）
-- JSONB — 存储二进制 JSON（支持索引、更高效，推荐）

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB
);

INSERT INTO products VALUES
(1, '手机', '{"brand": "华为", "price": 4999, "colors": ["黑", "白"]}'),
(2, '电脑', '{"brand": "Apple", "price": 12999, "colors": ["银", "灰"]}');

-- JSON 查询
SELECT name, attributes -> 'brand' AS brand FROM products;
-- 手机 "华为"
SELECT name, attributes ->> 'brand' AS brand FROM products;
-- 手机 华为（去引号）

-- 按 JSON 字段过滤
SELECT * FROM products WHERE attributes @> '{"brand": "华为"}';

-- JSON 更新
UPDATE products
SET attributes = jsonb_set(attributes, '{price}', '5999')
WHERE id = 1;

-- 为 JSONB 创建索引
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- JSON 聚合
SELECT jsonb_object_agg(name, attributes -> 'price') FROM products;
-- {"手机": 4999, "电脑": 12999}

数组类型

-- 定义数组类型列
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]              -- 字符串数组
);

-- 插入数组
INSERT INTO articles VALUES
(1, 'PostgreSQL 入门', ARRAY['数据库', 'PostgreSQL', '教程']),
(2, 'Java 基础', '{Java, OOP, 入门}');  -- 也可以用花括号

-- 访问数组元素（索引从 1 开始）
SELECT tags[1] FROM articles WHERE id = 1;  -- 数据库
SELECT tags[1:2] FROM articles WHERE id = 1; -- {数据库, PostgreSQL}

-- 数组查询
SELECT * FROM articles WHERE 'PostgreSQL' = ANY(tags);
SELECT * FROM articles WHERE tags @> ARRAY['数据库'];

-- 数组函数
SELECT array_length(tags, 1) FROM articles;  -- 数组长度
SELECT unnest(tags) FROM articles;           -- 展开数组为多行

范围类型

-- 创建范围类型表
CREATE TABLE reservations (
    room_id INT,
    during TSRANGE  -- 时间范围
);

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

-- 范围查询
SELECT * FROM reservations
WHERE during @> '2024-01-15 10:00'::timestamp;  -- 包含

SELECT * FROM reservations
WHERE during && '[2024-01-15 08:00, 2024-01-15 11:00)'::tsrange;  -- 重叠

-- 范围函数
SELECT lower(during), upper(during) FROM reservations;

网络地址类型

CREATE TABLE servers (
    hostname TEXT,
    ip INET,           -- IPv4/IPv6 地址
    mask CIDR,         -- CIDR 地址块
    mac MACADDR        -- MAC 地址
);

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

-- 网络函数
SELECT ip <<= '192.168.1.0/24' FROM servers;  -- 判断是否在子网中
SELECT host(ip) FROM servers;                  -- 去掉掩码

UUID 类型

-- 需要 uuid-ossp 扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    title TEXT,
    content TEXT
);

INSERT INTO documents (title, content) VALUES
('文档1', '内容');

SELECT * FROM documents;
-- id: f47ac10b-58cc-4372-a567-0e02b2c3d479

全文检索类型

-- tsvector — 文档向量
-- tsquery — 搜索查询

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT,
    fts TSVECTOR GENERATED ALWAYS AS (to_tsvector('simple', title || ' ' || body)) STORED
);

CREATE INDEX idx_fts ON documents USING GIN (fts);

INSERT INTO documents (title, body) VALUES
('PostgreSQL Tutorial', 'PostgreSQL is a powerful database');

-- 全文检索
SELECT * FROM documents
WHERE fts @@ to_tsquery('simple', 'PostgreSQL & database');

练习

-- 1. 创建一个使用 JSONB 存储用户扩展信息的表
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100),
    profile JSONB DEFAULT '{}'::jsonb
);

INSERT INTO users (name, profile) VALUES
('张三', '{"age": 25, "city": "北京", "skills": ["Java", "SQL"]}'),
('李四', '{"age": 30, "city": "上海", "skills": ["Python", "PostgreSQL"]}');

-- 查询会 Java 的用户
SELECT * FROM users WHERE profile @> '{"skills": ["Java"]}';

-- 2. 创建一个带标签数组的文章表
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    tags TEXT[] DEFAULT '{}'
);

INSERT INTO posts (title, tags) VALUES
('PostgreSQL 入门', ARRAY['数据库', '教程']),
('JSON 高级用法', ARRAY['JSON', 'PostgreSQL']);

PostgreSQL 数据类型详解

详情