Postgres基础知识学习
# 一、PostgreSQL 类型系统整体结构
PostgreSQL 的类型系统非常丰富，并允许用户通过 CREATE TYPE 定义新的数据类型。 (PostgreSQL (opens new window))
官方类型体系大致分为：
基础类型（Standard SQL Types）
│
├─ 数值类型
├─ 字符类型
├─ 日期时间
├─ 布尔类型
│
Postgres 特有类型
│
├─ JSON / JSONB
├─ Array
├─ Range / Multirange
├─ Geometric
├─ Network Address
├─ UUID
├─ Full-Text Search
├─ XML
│
复杂类型
│
├─ Composite
├─ Domain
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Postgres 设计目标之一是：
让数据库不仅仅是关系数据，还支持多模型数据结构。
# 二、基础数据类型（Standard SQL Types）
这些类型基本符合 SQL 标准。
# 1 数值类型 (Numeric Types)
| 类型 | 描述 |
|---|---|
| smallint | 2字节整数 |
| integer | 4字节整数 |
| bigint | 8字节整数 |
| numeric(p,s) | 任意精度数 |
| real | 单精度浮点 |
| double precision | 双精度浮点 |
示例：
CREATE TABLE products (
id SERIAL,
price NUMERIC(10,2),
stock INTEGER
);
1
2
3
4
5
2
3
4
5
特点：
numeric用于 金融计算float用于 科学计算
# 2 自增类型
Postgres提供：
serial
smallserial
bigserial
1
2
3
2
3
本质：
sequence + default
1
例如：
id SERIAL
1
等价：
CREATE SEQUENCE
DEFAULT nextval()
1
2
2
# 3 字符串类型
| 类型 | 说明 |
|---|---|
| char(n) | 固定长度 |
| varchar(n) | 可变长度 |
| text | 无限长度 |
推荐实践：
text = varchar
1
Postgres 不限制 text 性能。
# 4 二进制类型
bytea
1
用于：
- 文件
- 图片
- 二进制数据
示例：
INSERT INTO files(data)
VALUES (decode('FFD8FFE0','hex'));
1
2
2
# 5 日期时间类型
| 类型 | 说明 |
|---|---|
| date | 日期 |
| time | 时间 |
| timestamp | 日期时间 |
| timestamptz | 带时区时间 |
| interval | 时间间隔 |
示例：
SELECT now();
1
推荐：
timestamptz
1
避免时区问题。
# 6 Boolean 类型
boolean
1
值：
true
false
null
1
2
3
2
3
示例：
SELECT TRUE;
1
# 三、Postgres 特有类型（特色能力）
这些类型是 Postgres 成为 多模型数据库 的关键。
# 1 JSON / JSONB 类型
Postgres 提供：
json
jsonb
1
2
2
区别：
| 类型 | 特点 |
|---|---|
| json | 文本存储 |
| jsonb | 二进制解析存储 |
jsonb：
- 查询更快
- 支持索引 (PostgreSQL (opens new window))
示例：
CREATE TABLE users (
id INT,
profile JSONB
);
1
2
3
4
2
3
4
插入：
INSERT INTO users VALUES
(1,'{"name":"tom","age":20}');
1
2
2
查询字段：
SELECT profile->>'name'
FROM users;
1
2
2
JSONB 操作符：
| 操作符 | 含义 |
|---|---|
| -> | JSON对象 |
| ->> | 文本 |
| @> | 包含 |
| ? | key存在 |
示例：
SELECT *
FROM users
WHERE profile @> '{"age":20}';
1
2
3
2
3
# jsonpath 查询
Postgres 支持 SQL/JSON Path：
SELECT jsonb_path_query(profile,'$.name')
FROM users;
1
2
2
支持：
wildcard
recursive search
array access
1
2
3
2
3
# 2 Array 类型
Postgres 支持数组列。
CREATE TABLE posts(
tags TEXT[]
);
1
2
3
2
3
插入：
INSERT INTO posts VALUES
('{react,frontend}');
1
2
2
访问元素：
SELECT tags[1]
FROM posts;
1
2
2
查询数组：
SELECT *
FROM posts
WHERE 'react' = ANY(tags);
1
2
3
2
3
数组搜索：
SELECT *
FROM posts
WHERE tags @> ARRAY['react'];
1
2
3
2
3
官方提示：
数组不是集合模型。
# 3 Range 类型
Postgres 独有。
表示：
区间
1
例如：
时间段
数值范围
1
2
2
内置 range：
int4range
int8range
numrange
tsrange
tstzrange
daterange
1
2
3
4
5
6
2
3
4
5
6
示例：
CREATE TABLE booking(
period TSRANGE
);
1
2
3
2
3
插入：
INSERT INTO booking VALUES
('[2024-01-01,2024-01-10]');
1
2
2
判断重叠：
SELECT *
FROM booking
WHERE period && '[2024-01-05,2024-01-07]';
1
2
3
2
3
优点：
自动处理区间逻辑
1
# 4 Multirange
一个字段表示多个区间。
{[1,5),[10,20)}
1
用于：
时间碎片
库存时间段
1
2
2
# 5 UUID 类型
唯一标识：
uuid
1
示例：
SELECT gen_random_uuid();
1
优点：
分布式 ID
1
# 6 Network 类型
网络地址支持：
| 类型 |
|---|
| inet |
| cidr |
| macaddr |
示例：
SELECT inet '192.168.0.1';
1
用途：
IP日志
网络系统
1
2
2
# 7 几何类型
Postgres 内置几何计算：
| 类型 |
|---|
| point |
| line |
| circle |
| polygon |
| box |
示例：
SELECT point '(1,2)';
1
用于：
简单 GIS
1
# 8 全文检索类型
支持搜索：
| 类型 |
|---|
| tsvector |
| tsquery |
示例：
SELECT to_tsvector('postgres is awesome');
1
查询：
SELECT *
FROM articles
WHERE to_tsvector(content)
@@ to_tsquery('postgres');
1
2
3
4
2
3
4
# 9 XML 类型
xml
1
示例：
SELECT xpath('/book/title', xmlcol);
1
# 10 Domain 类型
Domain 是：
带约束的类型
1
示例：
CREATE DOMAIN email AS TEXT
CHECK (VALUE LIKE '%@%');
1
2
2
# 11 Composite 类型
类似：
struct
1
示例：
CREATE TYPE address AS (
city TEXT,
zip TEXT
);
1
2
3
4
2
3
4
使用：
CREATE TABLE users(
addr address
);
1
2
3
2
3
# 四、高级查询能力
Postgres 查询能力非常强。
# 1 CTE（Common Table Expression）
WITH
1
示例：
WITH recent_orders AS (
SELECT *
FROM orders
WHERE created_at > now() - interval '7 days'
)
SELECT *
FROM recent_orders;
1
2
3
4
5
6
7
2
3
4
5
6
7
优点：
可读性
模块化SQL
1
2
2
# 2 递归查询（Recursive Query）
用于：
树
图
组织架构
1
2
3
2
3
示例：
WITH RECURSIVE tree AS (
SELECT id,parent_id
FROM category
WHERE id=1
UNION ALL
SELECT c.id,c.parent_id
FROM category c
JOIN tree t ON c.parent_id=t.id
)
SELECT * FROM tree;
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 3 Window Functions
窗口函数是 Postgres 强项。
示例：
SELECT
id,
salary,
rank() OVER (ORDER BY salary DESC)
FROM employees;
1
2
3
4
5
2
3
4
5
常见：
rank()
dense_rank()
row_number()
lag()
lead()
sum() OVER
1
2
3
4
5
6
2
3
4
5
6
# 4 LATERAL 查询
LATERAL 允许子查询引用前表。
SELECT *
FROM users
CROSS JOIN LATERAL
jsonb_each(users.profile);
1
2
3
4
2
3
4
常用于：
JSON拆解
数组展开
1
2
2
# 5 JSON 查询
Postgres JSON 查询非常强。
示例：
SELECT *
FROM orders
WHERE data->'customer'->>'name' = 'Tom';
1
2
3
2
3
数组展开：
SELECT jsonb_array_elements(items)
FROM orders;
1
2
2
# 6 ARRAY 查询
SELECT unnest(tags)
FROM posts;
1
2
2
展开数组。
# 7 Range 查询
判断包含：
SELECT *
FROM booking
WHERE period @> timestamp '2024-01-05';
1
2
3
2
3
判断重叠：
&&
1
# 8 Full Text Search
SELECT *
FROM docs
WHERE to_tsvector(content)
@@ to_tsquery('database & postgres');
1
2
3
4
2
3
4
# 五、Postgres 类型系统设计思想
核心思想：
# 1 强类型系统
比 MySQL 强。
例如：
range
json
array
network
geometry
1
2
3
4
5
2
3
4
5
# 2 可扩展
用户可以：
CREATE TYPE
1
扩展：
- PostGIS
- pgvector
- citext
# 3 多模型数据库
支持：
relational
document
graph
search
GIS
1
2
3
4
5
2
3
4
5
# 六、总结
Postgres 类型系统 + 查询能力 = 非常强。
核心能力：
# 基础类型
numeric
string
date/time
boolean
binary
1
2
3
4
5
2
3
4
5
# 特有类型
JSONB
Array
Range
UUID
Network
Geometric
Full Text
XML
Composite
Domain
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
上次更新: 2026/03/16, 05:02:15