Glittering's blog

Appearance

一、PostgreSQL 类型系统整体结构 ​

PostgreSQL 的类型系统非常丰富，并允许用户通过 CREATE TYPE 定义新的数据类型。 (PostgreSQL)

官方类型体系大致分为：

基础类型（Standard SQL Types）
│
├─ 数值类型
├─ 字符类型
├─ 日期时间
├─ 布尔类型
│
Postgres 特有类型
│
├─ JSON / JSONB
├─ Array
├─ Range / Multirange
├─ Geometric
├─ Network Address
├─ UUID
├─ Full-Text Search
├─ XML
│
复杂类型
│
├─ Composite
├─ Domain

Postgres 设计目标之一是：

让数据库不仅仅是关系数据，还支持多模型数据结构。

二、基础数据类型（Standard SQL Types） ​

这些类型基本符合 SQL 标准。

1 数值类型 (Numeric Types) ​

类型描述
smallint2字节整数
integer4字节整数
bigint8字节整数
numeric(p,s)任意精度数
real单精度浮点
double precision双精度浮点

示例：

sql
CREATE TABLE products (
  id SERIAL,
  price NUMERIC(10,2),
  stock INTEGER
);

特点：

  • numeric 用于 金融计算
  • float 用于 科学计算

2 自增类型 ​

Postgres提供：

serial
smallserial
bigserial

本质：

sequence + default

例如：

sql
id SERIAL

等价：

CREATE SEQUENCE
DEFAULT nextval()

3 字符串类型 ​

类型说明
char(n)固定长度
varchar(n)可变长度
text无限长度

推荐实践：

text = varchar

Postgres 不限制 text 性能。

4 二进制类型 ​

bytea

用于：

  • 文件
  • 图片
  • 二进制数据

示例：

sql
INSERT INTO files(data)
VALUES (decode('FFD8FFE0','hex'));

5 日期时间类型 ​

类型说明
date日期
time时间
timestamp日期时间
timestamptz带时区时间
interval时间间隔

示例：

sql
SELECT now();

推荐：

timestamptz

避免时区问题。

6 Boolean 类型 ​

boolean

值：

true
false
null

示例：

sql
SELECT TRUE;

三、Postgres 特有类型（特色能力） ​

这些类型是 Postgres 成为 多模型数据库 的关键。

1 JSON / JSONB 类型 ​

Postgres 提供：

json
jsonb

区别：

类型特点
json文本存储
jsonb二进制解析存储

jsonb：

示例：

sql
CREATE TABLE users (
  id INT,
  profile JSONB
);

插入：

sql
INSERT INTO users VALUES
(1,'{"name":"tom","age":20}');

查询字段：

sql
SELECT profile->>'name'
FROM users;

JSONB 操作符：

操作符含义
->JSON对象
->>文本
@>包含
?key存在

示例：

sql
SELECT *
FROM users
WHERE profile @> '{"age":20}';

jsonpath 查询 ​

Postgres 支持 SQL/JSON Path：

sql
SELECT jsonb_path_query(profile,'$.name')
FROM users;

支持：

wildcard
recursive search
array access

2 Array 类型 ​

Postgres 支持数组列。

sql
CREATE TABLE posts(
  tags TEXT[]
);

插入：

sql
INSERT INTO posts VALUES
('{react,frontend}');

访问元素：

sql
SELECT tags[1]
FROM posts;

查询数组：

sql
SELECT *
FROM posts
WHERE 'react' = ANY(tags);

数组搜索：

sql
SELECT *
FROM posts
WHERE tags @> ARRAY['react'];

官方提示：

数组不是集合模型。

3 Range 类型 ​

Postgres 独有。

表示：

区间

例如：

时间段
数值范围

内置 range：

int4range
int8range
numrange
tsrange
tstzrange
daterange

示例：

sql
CREATE TABLE booking(
  period TSRANGE
);

插入：

sql
INSERT INTO booking VALUES
('[2024-01-01,2024-01-10]');

判断重叠：

sql
SELECT *
FROM booking
WHERE period && '[2024-01-05,2024-01-07]';

优点：

自动处理区间逻辑

4 Multirange ​

一个字段表示多个区间。

{[1,5),[10,20)}

用于：

时间碎片
库存时间段

5 UUID 类型 ​

唯一标识：

uuid

示例：

sql
SELECT gen_random_uuid();

优点：

分布式 ID

6 Network 类型 ​

网络地址支持：

类型
inet
cidr
macaddr

示例：

sql
SELECT inet '192.168.0.1';

用途：

IP日志
网络系统

7 几何类型 ​

Postgres 内置几何计算：

类型
point
line
circle
polygon
box

示例：

sql
SELECT point '(1,2)';

用于：

简单 GIS

8 全文检索类型 ​

支持搜索：

类型
tsvector
tsquery

示例：

sql
SELECT to_tsvector('postgres is awesome');

查询：

sql
SELECT *
FROM articles
WHERE to_tsvector(content)
@@ to_tsquery('postgres');

9 XML 类型 ​

xml

示例：

sql
SELECT xpath('/book/title', xmlcol);

10 Domain 类型 ​

Domain 是：

带约束的类型

示例：

sql
CREATE DOMAIN email AS TEXT
CHECK (VALUE LIKE '%@%');

11 Composite 类型 ​

类似：

struct

示例：

sql
CREATE TYPE address AS (
  city TEXT,
  zip TEXT
);

使用：

sql
CREATE TABLE users(
  addr address
);

四、高级查询能力 ​

Postgres 查询能力非常强。

1 CTE（Common Table Expression） ​

WITH

示例：

sql
WITH recent_orders AS (
  SELECT *
  FROM orders
  WHERE created_at > now() - interval '7 days'
)
SELECT *
FROM recent_orders;

优点：

可读性
模块化SQL

2 递归查询（Recursive Query） ​

用于：

树
图
组织架构

示例：

sql
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;

3 Window Functions ​

窗口函数是 Postgres 强项。

示例：

sql
SELECT
  id,
  salary,
  rank() OVER (ORDER BY salary DESC)
FROM employees;

常见：

rank()
dense_rank()
row_number()
lag()
lead()
sum() OVER

4 LATERAL 查询 ​

LATERAL 允许子查询引用前表。

sql
SELECT *
FROM users
CROSS JOIN LATERAL
jsonb_each(users.profile);

常用于：

JSON拆解
数组展开

5 JSON 查询 ​

Postgres JSON 查询非常强。

示例：

sql
SELECT *
FROM orders
WHERE data->'customer'->>'name' = 'Tom';

数组展开：

sql
SELECT jsonb_array_elements(items)
FROM orders;

6 ARRAY 查询 ​

sql
SELECT unnest(tags)
FROM posts;

展开数组。

7 Range 查询 ​

判断包含：

sql
SELECT *
FROM booking
WHERE period @> timestamp '2024-01-05';

判断重叠：

&&

8 Full Text Search ​

sql
SELECT *
FROM docs
WHERE to_tsvector(content)
@@ to_tsquery('database & postgres');

五、Postgres 类型系统设计思想 ​

核心思想：

1 强类型系统 ​

比 MySQL 强。

例如：

range
json
array
network
geometry

2 可扩展 ​

用户可以：

CREATE TYPE

扩展：

  • PostGIS
  • pgvector
  • citext

3 多模型数据库 ​

支持：

relational
document
graph
search
GIS

六、总结 ​

Postgres 类型系统 + 查询能力 = 非常强。

核心能力：

基础类型 ​

numeric
string
date/time
boolean
binary

特有类型 ​

JSONB
Array
Range
UUID
Network
Geometric
Full Text
XML
Composite
Domain

Copyright © 2026