PostgreSQL 数据类型 #
数据类型概述 #
PostgreSQL 提供了丰富的数据类型，是所有关系型数据库中最全面的。
text
┌─────────────────────────────────────────────────────────────┐
│ PostgreSQL 数据类型 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 数值类型 ├── 整数、自增整数、小数 │
│ 字符类型 ├── 定长、变长、无限制 │
│ 布尔类型 ─── true/false/null │
│ 日期时间 ├── date, time, timestamp, interval │
│ JSON类型 ├── json, jsonb │
│ 数组类型 ─── 任意类型的数组 │
│ 网络地址 ├── inet, cidr, macaddr │
│ 几何类型 ├── point, line, polygon │
│ UUID类型 ─── 通用唯一标识符 │
│ 其他类型 ├── XML, 货币, 位串, 范围类型 │
│ │
└─────────────────────────────────────────────────────────────┘
数值类型 #
整数类型 #
sql
-- 整数类型对比
┌──────────────┬────────┬─────────────────────────────────────────┐
│ 类型 │ 字节 │ 范围 │
├──────────────┼────────┼─────────────────────────────────────────┤
│ SMALLINT │ 2 │ -32768 到 +32767 │
│ INTEGER │ 4 │ -2147483648 到 +2147483647 │
│ BIGINT │ 8 │ -9223372036854775808 到 +9223372036854775807 │
└──────────────┴────────┴─────────────────────────────────────────┘
-- 创建表
CREATE TABLE numbers (
id SERIAL PRIMARY KEY,
small_num SMALLINT,
normal_num INTEGER,
big_num BIGINT
);
-- 插入数据
INSERT INTO numbers (small_num, normal_num, big_num) VALUES
(100, 100000, 10000000000),
(-32768, -2147483648, -9223372036854775808);
-- 超出范围会报错
-- INSERT INTO numbers (small_num) VALUES (32769); -- 错误！
自增整数（Serial） #
sql
-- 自增类型对比
┌──────────────┬───────────────────────────────────────────────┐
│ 类型 │ 说明 │
├──────────────┼───────────────────────────────────────────────┤
│ SMALLSERIAL │ 自增 smallint，范围 1 到 32767 │
│ SERIAL │ 自增 integer，范围 1 到 2147483647 │
│ BIGSERIAL │ 自增 bigint，范围 1 到 9223372036854775807 │
└──────────────┴───────────────────────────────────────────────┘
-- SERIAL 等价于
CREATE TABLE users (
id SERIAL PRIMARY KEY, -- 自动创建序列 users_id_seq
name VARCHAR(100)
);
-- 等价于
CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
id INTEGER NOT NULL DEFAULT nextval('users_id_seq') PRIMARY KEY,
name VARCHAR(100)
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;
-- 插入数据（id 自动生成）
INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');
-- 查看当前值
SELECT currval('users_id_seq'); -- 最近获取的值
SELECT lastval(); -- 任何序列最近获取的值
-- 重置序列
ALTER SEQUENCE users_id_seq RESTART WITH 1;
小数类型 #
sql
-- 小数类型对比
┌─────────────────┬────────┬─────────────────────────────────────┐
│ 类型 │ 字节 │ 说明 │
├─────────────────┼────────┼─────────────────────────────────────┤
│ DECIMAL(p,s) │ 可变 │ 精确小数，p精度，s小数位 │
│ NUMERIC(p,s) │ 可变 │ 同 DECIMAL │
│ REAL │ 4 │ 单精度浮点，6位有效数字 │
│ DOUBLE PRECISION│ 8 │ 双精度浮点，15位有效数字 │
└─────────────────┴────────┴─────────────────────────────────────┘
-- 精确小数（适合货币）
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10, 2), -- 最多10位，小数点后2位
discount NUMERIC(5, 4) -- 最多5位，小数点后4位
);
INSERT INTO products (name, price, discount) VALUES
('Product A', 99.99, 0.1500),
('Product B', 1234.56, 0.2500);
-- 浮点数（适合科学计算）
CREATE TABLE measurements (
id SERIAL PRIMARY KEY,
temperature REAL,
pressure DOUBLE PRECISION
);
-- 注意浮点精度问题
SELECT 0.1::REAL + 0.2::REAL; -- 可能不是精确的 0.3
SELECT 0.1::DECIMAL + 0.2::DECIMAL; -- 精确的 0.3
字符类型 #
字符类型对比 #
sql
-- 字符类型对比
┌─────────────────┬─────────────────────────────────────────────┐
│ 类型 │ 说明 │
├─────────────────┼─────────────────────────────────────────────┤
│ CHAR(n) │ 定长，不足补空格，最大 1GB │
│ VARCHAR(n) │ 变长，有长度限制，最大 1GB │
│ TEXT │ 变长，无长度限制，最大 1GB │
└─────────────────┴─────────────────────────────────────────────┘
-- 创建表
CREATE TABLE texts (
id SERIAL PRIMARY KEY,
code CHAR(10), -- 固定10位，不足补空格
name VARCHAR(100), -- 最多100字符
description TEXT -- 无限制
);
INSERT INTO texts (code, name, description) VALUES
('ABC', 'Product', 'A very long description...');
-- 查看存储差异
SELECT
code,
LENGTH(code) AS code_length, -- 10 (补了空格)
name,
LENGTH(name) AS name_length -- 7
FROM texts;
-- CHAR 会自动补空格
SELECT 'ABC'::CHAR(10) = 'ABC '::CHAR(10); -- true
-- VARCHAR 和 TEXT 不会补空格
SELECT 'ABC'::VARCHAR(10) = 'ABC '::VARCHAR(10); -- false
字符串函数 #
sql
-- 常用字符串函数
SELECT
LENGTH('Hello'), -- 5
CHAR_LENGTH('Hello'), -- 5
UPPER('hello'), -- HELLO
LOWER('HELLO'), -- hello
INITCAP('hello world'), -- Hello World
CONCAT('Hello', ' ', 'World'), -- Hello World
'Hello' || ' ' || 'World', -- Hello World
SUBSTRING('Hello World', 1, 5), -- Hello
SUBSTRING('Hello World' FROM 7), -- World
LEFT('Hello', 3), -- Hel
RIGHT('Hello', 3), -- llo
TRIM(' Hello '), -- Hello
LTRIM(' Hello'), -- Hello
RTRIM('Hello '), -- Hello
REPLACE('Hello', 'l', 'L'), -- HeLLo
POSITION('World' IN 'Hello World'), -- 7
SPLIT_PART('a,b,c', ',', 2), -- b
LPAD('5', 3, '0'), -- 005
RPAD('5', 3, '0'), -- 500
REVERSE('Hello'), -- olleH
REPEAT('Ab', 3); -- AbAbAb
布尔类型 #
sql
-- 布尔类型
-- 有效值：
-- true: TRUE, 'true', 't', 'yes', 'y', '1', 'on'
-- false: FALSE, 'false', 'f', 'no', 'n', '0', 'off'
CREATE TABLE tasks (
id SERIAL PRIMARY KEY,
title VARCHAR(200),
is_completed BOOLEAN DEFAULT FALSE,
is_important BOOLEAN
);
-- 插入数据
INSERT INTO tasks (title, is_completed, is_important) VALUES
('Task 1', TRUE, 'yes'),
('Task 2', FALSE, 'no'),
('Task 3', 't', 'y'),
('Task 4', '1', '0');
-- 查询
SELECT * FROM tasks WHERE is_completed = TRUE;
SELECT * FROM tasks WHERE is_completed; -- 简写
SELECT * FROM tasks WHERE NOT is_completed; -- 未完成
-- 布尔运算
SELECT
TRUE AND FALSE, -- false
TRUE OR FALSE, -- true
NOT TRUE, -- false
TRUE IS TRUE, -- true
NULL IS TRUE, -- false
NULL IS NOT TRUE, -- true (包括 false 和 null)
TRUE IS NOT NULL; -- true
日期时间类型 #
日期时间类型对比 #
sql
-- 日期时间类型对比
┌──────────────┬────────┬─────────────────────────────────────────┐
│ 类型 │ 字节 │ 说明 │
├──────────────┼────────┼─────────────────────────────────────────┤
│ DATE │ 4 │ 日期（年月日） │
│ TIME │ 8 │ 时间（时分秒） │
│ TIMETZ │ 12 │ 带时区的时间 │
│ TIMESTAMP │ 8 │ 日期和时间 │
│ TIMESTAMPTZ │ 8 │ 带时区的日期和时间 │
│ INTERVAL │ 16 │ 时间间隔 │
└──────────────┴────────┴─────────────────────────────────────────┘
-- 创建表
CREATE TABLE events (
id SERIAL PRIMARY KEY,
event_date DATE,
event_time TIME,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
created_at_tz TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO events (event_date, event_time) VALUES
('2026-03-29', '14:30:00'),
(CURRENT_DATE, CURRENT_TIME);
日期时间函数 #
sql
-- 当前日期时间
SELECT
CURRENT_DATE, -- 2026-03-29
CURRENT_TIME, -- 14:30:00.123456+08
CURRENT_TIMESTAMP, -- 2026-03-29 14:30:00.123456+08
LOCALTIME, -- 14:30:00.123456
LOCALTIMESTAMP, -- 2026-03-29 14:30:00.123456
NOW(); -- 2026-03-29 14:30:00.123456+08
-- 日期时间运算
SELECT
CURRENT_DATE + INTERVAL '1 day',
CURRENT_DATE - INTERVAL '1 week',
CURRENT_TIMESTAMP + INTERVAL '1 month 2 days 3 hours',
CURRENT_TIMESTAMP - INTERVAL '1 year 6 months';
-- 提取部分
SELECT
EXTRACT(YEAR FROM CURRENT_TIMESTAMP), -- 2026
EXTRACT(MONTH FROM CURRENT_TIMESTAMP), -- 3
EXTRACT(DAY FROM CURRENT_TIMESTAMP), -- 29
EXTRACT(HOUR FROM CURRENT_TIMESTAMP), -- 14
EXTRACT(DOW FROM CURRENT_TIMESTAMP), -- 星期几 (0-6, 0=周日)
EXTRACT(DOY FROM CURRENT_TIMESTAMP), -- 一年中第几天
EXTRACT(WEEK FROM CURRENT_TIMESTAMP); -- 一年中第几周
-- 截断
SELECT
DATE_TRUNC('year', CURRENT_TIMESTAMP), -- 2026-01-01 00:00:00
DATE_TRUNC('month', CURRENT_TIMESTAMP), -- 2026-03-01 00:00:00
DATE_TRUNC('day', CURRENT_TIMESTAMP), -- 2026-03-29 00:00:00
DATE_TRUNC('hour', CURRENT_TIMESTAMP); -- 2026-03-29 14:00:00
-- 日期差值
SELECT
AGE(TIMESTAMP '2026-12-31', TIMESTAMP '2026-01-01'),
-- 11 mons 30 days
DATE_PART('day', TIMESTAMP '2026-12-31' - TIMESTAMP '2026-01-01');
-- 364
时间间隔（Interval） #
sql
-- 时间间隔格式
SELECT
INTERVAL '1 year',
INTERVAL '2 months',
INTERVAL '3 days',
INTERVAL '4 hours',
INTERVAL '5 minutes',
INTERVAL '6 seconds',
INTERVAL '1 year 2 months 3 days',
INTERVAL '1-2' YEAR TO MONTH, -- 1年2个月
INTERVAL '3 4:05:06' DAY TO SECOND; -- 3天4小时5分6秒
-- 实际应用
CREATE TABLE subscriptions (
id SERIAL PRIMARY KEY,
user_id INTEGER,
start_date DATE,
duration INTERVAL,
end_date DATE GENERATED ALWAYS AS (start_date + duration) STORED
);
INSERT INTO subscriptions (user_id, start_date, duration) VALUES
(1, '2026-01-01', INTERVAL '1 year'),
(2, '2026-03-01', INTERVAL '6 months');
JSON 类型 #
JSON vs JSONB #
sql
-- JSON 类型对比
┌─────────────┬─────────────────────────────────────────────────┐
│ 类型 │ 说明 │
├─────────────┼─────────────────────────────────────────────────┤
│ JSON │ 存储为文本，每次解析，保留原始格式 │
│ JSONB │ 存储为二进制，解析一次，支持索引，键顺序不保留 │
└─────────────┴─────────────────────────────────────────────────┘
-- 推荐：大多数情况使用 JSONB
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
profile JSONB
);
-- 插入 JSON 数据
INSERT INTO users (name, profile) VALUES
('Alice', '{"age": 25, "city": "Beijing", "skills": ["Python", "SQL"]}'),
('Bob', '{"age": 30, "city": "Shanghai", "skills": ["Java", "Go"]}');
JSON 操作符 #
sql
-- 提取值
SELECT
profile->>'age' AS age, -- 文本: "25"
profile->'age' AS age_json, -- JSON: 25
profile->'skills'->0 AS first_skill, -- JSON: "Python"
profile->'skills'->>0 AS first_skill_text; -- 文本: "Python"
-- 路径提取
SELECT profile#>'{skills,0}' FROM users; -- JSON 路径
SELECT profile#>>'{skills,0}' FROM users; -- 文本路径
-- 条件查询
SELECT * FROM users WHERE profile->>'city' = 'Beijing';
SELECT * FROM users WHERE profile->'age'::INTEGER > 25;
-- 包含检查
SELECT * FROM users WHERE profile @> '{"city": "Beijing"}';
SELECT * FROM users WHERE profile ? 'age'; -- 是否有 age 键
SELECT * FROM users WHERE profile ?| ARRAY['age', 'city']; -- 是否有任一键
SELECT * FROM users WHERE profile ?& ARRAY['age', 'city']; -- 是否有所有键
JSON 函数 #
sql
-- 构建 JSON
SELECT
json_build_object('name', 'Alice', 'age', 25),
-- {"name": "Alice", "age": 25}
json_build_array(1, 2, 3),
-- [1, 2, 3]
json_object('key1', 'value1', 'key2', 'value2');
-- {"key1": "value1", "key2": "value2"}
-- 聚合为 JSON
SELECT json_agg(name) FROM users;
-- ["Alice", "Bob"]
SELECT json_object_agg(name, profile) FROM users;
-- {"Alice": {...}, "Bob": {...}}
-- 修改 JSON
UPDATE users
SET profile = profile || '{"email": "alice@example.com"}'::jsonb;
UPDATE users
SET profile = profile - 'email'; -- 删除键
-- 展开 JSON
SELECT * FROM jsonb_each('{"a": 1, "b": 2}'::jsonb);
-- key | value
-- a | 1
-- b | 2
-- 获取键和值
SELECT
jsonb_object_keys(profile) AS keys
FROM users;
-- 设置嵌套值
UPDATE users
SET profile = jsonb_set(profile, '{skills,0}', '"JavaScript"');
数组类型 #
数组定义 #
sql
-- 定义数组列
CREATE TABLE projects (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
tags VARCHAR(50)[], -- 字符串数组
scores INTEGER[], -- 整数数组
coordinates NUMERIC(10,2)[][] -- 二维数组
);
-- 插入数据
INSERT INTO projects (name, tags, scores) VALUES
('Project A', ARRAY['web', 'frontend', 'react'], ARRAY[90, 85, 92]),
('Project B', '{"backend", "api", "node"}', '{80, 88, 75}');
数组操作 #
sql
-- 访问元素（索引从1开始）
SELECT
name,
tags[1] AS first_tag,
scores[1] AS first_score
FROM projects;
-- 数组切片
SELECT tags[1:2] FROM projects;
-- 获取数组长度
SELECT
name,
array_length(tags, 1) AS tag_count,
cardinality(tags) AS tag_count_v2;
-- 查询包含某元素
SELECT * FROM projects WHERE 'react' = ANY(tags);
SELECT * FROM projects WHERE tags @> ARRAY['react'];
SELECT * FROM projects WHERE 'react' IN (SELECT unnest(tags));
-- 查询包含所有元素
SELECT * FROM projects WHERE tags @> ARRAY['web', 'frontend'];
-- 数组连接
SELECT ARRAY[1, 2] || ARRAY[3, 4]; -- {1, 2, 3, 4}
SELECT ARRAY[1, 2] || 3; -- {1, 2, 3}
-- 展开数组
SELECT name, unnest(tags) AS tag FROM projects;
-- 聚合为数组
SELECT array_agg(name) FROM projects;
数组函数 #
sql
-- 常用数组函数
SELECT
array_length(ARRAY[1,2,3], 1), -- 3
cardinality(ARRAY[1,2,3]), -- 3
array_append(ARRAY[1,2], 3), -- {1,2,3}
array_prepend(0, ARRAY[1,2]), -- {0,1,2}
array_remove(ARRAY[1,2,2,3], 2), -- {1,3}
array_replace(ARRAY[1,2,3], 2, 5), -- {1,5,3}
array_position(ARRAY[1,2,3], 2), -- 2
array_positions(ARRAY[1,2,2,3], 2), -- {2,3}
array_to_string(ARRAY[1,2,3], ','), -- '1,2,3'
string_to_array('a,b,c', ','); -- {a,b,c}
其他常用类型 #
UUID 类型 #
sql
-- 启用 UUID 扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- 创建表
CREATE TABLE sessions (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
user_id INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 生成 UUID
SELECT uuid_generate_v4(); -- 随机 UUID
SELECT uuid_generate_v1(); -- 基于时间戳的 UUID
-- 直接使用 gen_random_uuid() (PostgreSQL 13+)
SELECT gen_random_uuid();
网络地址类型 #
sql
-- 网络地址类型
┌─────────────┬─────────────────────────────────────────────────┐
│ 类型 │ 说明 │
├─────────────┼─────────────────────────────────────────────────┤
│ INET │ IPv4 或 IPv6 主机地址 │
│ CIDR │ IPv4 或 IPv6 网络地址 │
│ MACADDR │ MAC 地址 │
│ MACADDR8 │ 8字节 MAC 地址 │
└─────────────┴─────────────────────────────────────────────────┘
CREATE TABLE networks (
id SERIAL PRIMARY KEY,
ip_address INET,
network CIDR,
mac_address MACADDR
);
INSERT INTO networks (ip_address, network, mac_address) VALUES
('192.168.1.100', '192.168.1.0/24', '08:00:2b:01:02:03'),
('2001:db8::1', '2001:db8::/32', '08-00-2b-01-02-03');
-- 网络操作
SELECT
ip_address,
network,
ip_address << network AS is_in_network, -- 是否在网络中
host(ip_address), -- 提取主机地址
netmask(network); -- 网络掩码
范围类型 #
sql
-- 内置范围类型
┌─────────────────┬─────────────────────────────────────────────┐
│ 类型 │ 说明 │
├─────────────────┼─────────────────────────────────────────────┤
│ INT4RANGE │ integer 范围 │
│ INT8RANGE │ bigint 范围 │
│ NUMRANGE │ numeric 范围 │
│ TSRANGE │ timestamp 范围（无时区） │
│ TSTZRANGE │ timestamptz 范围（有时区） │
│ DATERANGE │ date 范围 │
└─────────────────┴─────────────────────────────────────────────┘
CREATE TABLE reservations (
id SERIAL PRIMARY KEY,
room_id INTEGER,
during TSRANGE
);
INSERT INTO reservations (room_id, during) VALUES
(1, '[2026-03-29 14:00, 2026-03-29 18:00)'),
(2, '[2026-03-29 10:00, 2026-03-29 12:00]');
-- 范围操作
SELECT * FROM reservations
WHERE during && TSRANGE('[2026-03-29 15:00, 2026-03-29 16:00)');
-- && 表示重叠
-- 包含
SELECT * FROM reservations
WHERE during @> TIMESTAMP '2026-03-29 15:00';
类型转换 #
sql
-- 使用 ::type 语法
SELECT
'123'::INTEGER,
'2026-03-29'::DATE,
'{"a": 1}'::JSONB,
ARRAY[1, 2, 3]::VARCHAR[];
-- 使用 CAST 函数
SELECT
CAST('123' AS INTEGER),
CAST('2026-03-29' AS DATE);
-- 隐式转换
SELECT
123 || '456', -- '123456' (整数转文本)
CURRENT_DATE::TEXT; -- '2026-03-29'
学习路径 #
text
入门阶段
├── PostgreSQL简介
├── 安装与配置
├── 基础语法
└── 数据类型（本文）
基础阶段
├── 数据库操作
├── 表操作
├── 数据CRUD
└── 基础查询
下一步 #
了解了数据类型后，接下来学习 数据库操作，开始创建和管理数据库！
最后更新：2026-03-29