PostgreSQL

• https://www.postgresql.org/
• 枫枫知道 PostgreSql教程

PostgreSQL Vs MySQL

对比维度	MySQL	PostgreSQL
产品定位	轻量开源、互联网标配、主打简单 CRUD 高并发	企业级全能型、SQL 标准严格、复杂查询 / 高级特性拉满
架构层级	数据库 → 表，无独立 Schema 概念，库等价模式	数据库 → Schema 模式 → 表，三层结构，可多模式隔离表
上手难度	低，配置简单、语法通俗、新手友好	中等，概念多（库 / 模式分离）、规则严谨、学习成本稍高
资源占用	轻量，内存 / CPU 消耗低，低配服务器可流畅运行	偏重，默认内存占用更高，更吃硬件配置
数据类型	基础类型够用，JSON 支持一般，无原生数组、GIS 类型	支持数组、JSON/JSONB、GIS 地理、IP、区间、枚举等高级类型
数据严谨性	约束宽松，非法数据可能自动截断，易产生脏数据	严格遵循 SQL 标准，约束 / 外键强校验，拒绝脏数据，金融政务友好
事务 MVCC	InnoDB 支持事务、MVCC，基础够用	事务一致性更强，MVCC 机制更成熟，隔离级别更完善
SQL 能力	支持基础联表，CTE / 递归 / 窗口函数支持较弱	原生支持 CTE、递归查询、窗口函数、自定义函数、复杂子查询
性能表现	简单增删改查、高并发短事务性能优秀	复杂多表 JOIN、大表聚合、报表统计性能碾压 MySQL
并发能力	高并发简单读写场景优化成熟	并发也强，但复杂查询多的时候资源开销更大
扩展能力	扩展有限，依赖第三方插件	可自定义数据类型、函数、插件，原生支持时序 / 全文检索 / GIS
生态人才	生态极大、教程海量、运维开发人才遍地都是	生态逐年火爆，但资深运维和优化人才比 MySQL 少
ORM 适配	完美兼容 MyBatis、Prisma、Drizzle、JPA 等	全 ORM 兼容，Node/TS、Java、Go 项目都常用
部署运维	安装简单、配置少、备份迁移工具丰富	配置参数多、调优复杂，库与 Schema 概念易混淆
适用场景	后台管理、小程序、电商普通业务、高并发简单 CRUD	复杂报表、数据分析、GIS 地理、JSON 业务、树形层级、政务金融

基本结构

核心层级结构

逻辑结构从大到小依次为：实例 (Instance) > 数据库 (Database) > 模式 (Schema) > 表 (Table) > 行与列 (Row & Column)

PostgreSQL 比 MySQL 多一层 Schema

为了做逻辑隔离、命名隔离、权限隔离，不用频繁新建数据库

加一层Schema的优势在于

• 单库多模块：一个数据库里用不同 Schema 分用户、订单、库存等模块，不用像 MySQL 那样拆多个库
• 表名可重复：不同 Schema 可以有同名表，互不冲突，避免命名混乱
• 权限更精细：可以按模式单独给用户授权，只允许访问某个业务模块，比 MySQL 按整库授权更安全
• 无需跨库联表：所有业务在同一个库，跨模块连表查询简单、性能更好
• 便于管理维护：整个项目只用一个库，备份、迁移、清理只需要维护这一个库，不用管一堆分库

库操作

建库

create database db1;

删库&强制删除

drop database db1;
drop database db1 with (force);

查所有库

select datname
from pg_database;

创建模式

create SCHEMA IF NOT EXISTS storage;

删除模式

drop SCHEMA IF EXISTS storage;

级联删除：删除模式以及模式里的所有表、视图、函数等

drop schema if exists storage cascade;

表操作

建表

CREATE TABLE IF NOT EXISTS storage.files
(
    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 自增主键
    file_id    UUID        DEFAULT gen_random_uuid(),               -- uuid
    file_name  TEXT NOT NULL,                                       -- 不能为空
    file_size  BIGINT CHECK (file_size >= 0),                       -- 检查约束：大小不能为负
    is_public  BOOLEAN     DEFAULT false,                           -- 默认值
    tags       TEXT[],                                              -- 数组类型（PG特色！）
    created_at TIMESTAMPTZ DEFAULT NOW()                            -- 带时区的时间
);

查表

SELECT schemaname AS 模式名,
       tablename  AS 表名,
       tableowner AS 所有人
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

查表列结构

SELECT column_name              AS 字段名,
       data_type                AS 数据类型,
       is_nullable              AS 是否允许为空,
       column_default           AS 默认值,
       character_maximum_length AS 最大长度
FROM information_schema.columns
WHERE table_name = 'files'
ORDER BY ordinal_position; -- 按照表中定义的顺序排列

修改表结构

-- 加字段
ALTER TABLE storage.files
    ADD COLUMN download_count INT DEFAULT 0;
-- 改字段类型
ALTER TABLE storage.files
    ALTER COLUMN file_name TYPE VARCHAR(255);
-- 重命名列名
ALTER TABLE storage.files
    RENAME COLUMN is_public TO is_shared;
-- 删除列
ALTER TABLE storage.files
    DROP COLUMN tags;

删表

-- 基本删除
DROP TABLE IF EXISTS storage.files;

-- 级联删除（如果这个表被其他表作为外键关联，用CASCADE会连带删除关联）
DROP TABLE storage.files CASCADE;

清空表

TRUNCATE TABLE storage.files;
-- 清空表并且重置自增id
TRUNCATE TABLE storage.files i;

表的元指令

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'files';

表继承

-- 父表：定义基础字段
CREATE TABLE content
(
    title  TEXT,
    author TEXT
);

-- 子表：继承基础字段，并增加自己的字段
CREATE TABLE video
(
    duration INT
) INHERITS (content);

插入+回显

INSERT INTO storage.files (file_name, file_size, is_public, tags)
VALUES ('课程大纲.pdf', 1024576, true, ARRAY ['教育', 'PDF']);


INSERT INTO storage.files (file_name, file_size, tags)
VALUES ('demo.mp4', 50000000, '{"视频", "测试"}'),
       ('config.yaml', 1024, '{"配置"}');

-- 插入+回显
INSERT INTO storage.files (file_name, file_size)
VALUES ('db.zip', 1024 * 124 * 8)
RETURNING *;

查询

SELECT *
FROM storage.files;

SELECT *
FROM storage.files
WHERE '视频' = ANY (tags);

修改

UPDATE storage.files
SET is_public = false
WHERE file_name = 'demo.mp4';

删除

DELETE
FROM storage.files
WHERE file_id = '018a72a4-ab27-4944-a922-446bf36052ff';

表结构

常用数据类型

类型分类	数据类型	说明	举例
数值类型	smallint	短整型，占用 2 字节，取值范围小	100、-50
数值类型	integer / int	常规整型，开发最常用整数类型	2026、999
数值类型	bigint	大整型，适合存储超大整数值	9223372036854775807
数值类型	decimal(p,s)	高精度定点小数，财务金额专用，无精度丢失	decimal(10,2) 存 99.99
数值类型	numeric(p,s)	与 decimal 完全等价，适合精密计算场景	numeric(8,3) 存 123.456
数值类型	real	单精度浮点数，精度较低，不建议存金额	3.14、0.618
数值类型	double precision	双精度浮点数，小数近似存储	3.1415926
数值类型	serial	自增整型，等价自增主键	自动生成 1,2,3,4...
数值类型	bigserial	大自增整型，适用于数据量极大的自增场景	自动生成超大自增序列
字符文本	char(n)	固定长度字符串，长度不足自动补空格	char(6) 存 'abc' 自动补空格到 6 位
字符文本	varchar(n)	可变长度字符串，可限制最大长度	varchar(50) 存 '张三'
字符文本	text	无长度限制长文本，适合存大段文字	文章内容、备注长文本
字符文本	citext	不区分大小写的文本类型，匹配忽略大小写	查询 Admin 和 admin 视为相同
日期时间	date	仅存储年月日日期	'2026-05-11'
日期时间	time	仅存储时分秒时间	'14:30:59'
日期时间	timestamp	日期 + 时间，不带时区	'2026-05-11 14:30:59'
日期时间	timestamptz	日期 + 时间 + 时区，项目推荐优先使用	'2026-05-11 14:30:59+08'
日期时间	interval	时间间隔类型，可存储几天、几小时等时间跨度	'3 days'、'2 hours 30 min'
基础布尔	boolean	布尔类型，取值 true /false	true、false
特色高级	json	文本格式 JSON，读取解析慢，无索引优化	'{""name"":""张三"",""age"":20}'
特色高级	jsonb	二进制 JSON，支持索引、查询高效，业务首选	'{""status"":1,""list"":[1,2,3]}'
特色高级	uuid	全局唯一标识，分布式系统主键首选	'550e8400-e29b-41d4-a716-446655440000'
特色高级	array	数组类型，支持 int []、text [] 等任意类型数组	array[1,2,3]、array['男','女']
特色高级	point	坐标点类型，用于 GIS 地理位置存储	'(116.40,39.90)' 经纬度
特色高级	inet	专门存储 IP 地址，自带 IP 校验和查询能力	'192.168.1.100'
特色高级	macaddr	存储网卡 MAC 地址	'08:00:2b:01:02:03'
特色高级	tsvector	全文检索专用分词类型，适配 PG 全文搜索	'中文 全文 检索' 分词向量

列约束

约束类型	描述	示例
NOT NULL	列不允许为 NULL，必须填写值	name VARCHAR(50) NOT NULL
UNIQUE	列的值必须唯一，不能重复	phone VARCHAR(20) UNIQUE
PRIMARY KEY	主键，唯一标识一行，等价 NOT NULL + UNIQUE	id INT PRIMARY KEY
FOREIGN KEY	外键，关联另一张表的主键	user_id INT REFERENCES users(id)
CHECK	自定义条件检查，满足条件才能写入	age INT CHECK (age >= 18)
DEFAULT	未指定值时，使用默认值	status INT DEFAULT 1
SERIAL	自增整数（简化主键写法）	id SERIAL PRIMARY KEY

单表查询

模糊、范围查询

-- 查找所有 png 图片，ilike表示忽略大小写
select *
from "storage".file_details fd
where fd.file_name ilike '%png';

-- 查找 2026 年上传的文件
select *
from "storage".file_details fd
where fd.created_at >= '2026-01-01'
  and fd.created_at < '2027-01-01';

select *
from "storage".file_details fd
where EXTRACT(YEAR FROM fd.created_at) = 2026;

数组类型的深度检索

-- tag中有一个是文档的
select *
from "storage".file_details fd
where '文档' = any (fd.tags);
-- tag中同时包含素材和封面
select *
from "storage".file_details fd
where fd.tags @> Array ['素材','封面'];
-- tag中包含素材或者封面
select *
from "storage".file_details fd
where fd.tags && Array ['素材','封面'];

jsonb操作

-- `->>`提取出来的是text，`->`提取出来的是json对象
-- 提取：查找作者是 "modify" 的文件
select *
from "storage".file_details fd
where fd.metadata ->> 'author' = 'modify';

-- 如果取出来的字段还是一个json，就得用->取了
select *
from "storage".file_details fd
where (fd.metadata -> 'video' -> 'resolution' ->> 'width') :: int = 1920;

-- 键值对存在性判断：查询所有定义了 "color" 属性的文件
select *
from "storage".file_details fd
where fd.metadata ? 'color';

网络地址

-- 精确IP匹配【略】
-- 范围匹配：查找属于 172.16.0.0/16 网段的所有上传记录
select *
from "storage".file_details fd
where fd.uploader_ip << '172.16.0.0/16';

排序分页

SELECT *
FROM "storage".file_details
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

聚合统计

-- 统计每个 IP 上传了多少次，并计算每个 IP 上传文件包含的总标签数
SELECT uploader_ip,
       COUNT(*)                   AS upload_count,
       SUM(array_length(tags, 1)) AS total_tags
FROM "storage".file_details
GROUP BY uploader_ip;

数据转换&格式化

-- 格式化输出：ID 前 8 位 + 文件名 + 简写日期
SELECT left(detail_id::text, 8)          AS short_id,
       file_name,
       to_char(created_at, 'YYYY-MM-DD') AS simple_date
FROM "storage".file_details;

函数

函数分类	函数写法	功能说明	简单示例
字符串函数	concat(s1,s2,...)	拼接多个字符串	concat('张','三') → 张三
字符串函数	concat_ws(sep,s1,s2)	带分隔符拼接	concat_ws('-','2026','05','11')
字符串函数	length(str)	获取字符串字符长度	length('abc') → 3
字符串函数	upper(str)	转大写	upper('abc') → ABC
字符串函数	lower(str)	转小写	lower('ABC') → abc
字符串函数	trim(str)	去除首尾空格	trim(' test ') → test
字符串函数	ltrim(str)	去除左侧空格	ltrim(' abc') → abc
字符串函数	rtrim(str)	去除右侧空格	rtrim('abc ') → abc
字符串函数	substring(str from n for len)	截取子串	substring('12345' from 1 for 2) → 12
字符串函数	replace(str,old,new)	替换指定字符	replace('a-b','-','') → ab
字符串函数	position(sub in str)	查找子串起始位置	position('b' in 'abc') → 2
字符串函数	split_part(str,sep,num)	按分隔符拆分取值	split_part('a,b,c',',',2) → b
数值函数	abs(num)	取绝对值	abs(-10) → 10
数值函数	round(num)	四舍五入取整	round(3.6) → 4
数值函数	round(num,d)	保留 d 位小数四舍五入	round(3.1415,2) → 3.14
数值函数	ceil(num)	向上取整	ceil(3.1) → 4
数值函数	floor(num)	向下取整	floor(3.9) → 3
数值函数	mod(a,b)	取模 / 取余	mod(10,3) → 1
数值函数	power(a,b)	计算 a 的 b 次方	power(2,3) → 8
数值函数	sqrt(num)	开平方	sqrt(16) → 4
数值函数	random()	生成 0~1 随机小数	random() → 0.xxx
日期时间函数	now()	获取当前带时区时间戳	now() → 2026-05-11 …
日期时间函数	current_date	获取当前年月日	current_date → 2026-05-11
日期时间函数	current_time	获取当前时分秒	current_time → 15:30:20
日期时间函数	current_timestamp	当前完整时间戳	current_timestamp
日期时间函数	date_part(单位,时间)	提取年 / 月 / 日 / 时 / 分 / 秒	date_part('year',now())
日期时间函数	age(t1,t2)	计算两个日期时间间隔	age('2026-01-01','2025-01-01')
日期时间函数	时间 + interval	日期加减时间间隔	now() + interval '3 day'
日期时间函数	to_char(时间,格式)	时间转格式化字符串	to_char(now(),'yyyy-MM-dd')
日期时间函数	to_timestamp(str)	字符串转为时间戳	to_timestamp('2026-05-11','yyyy-MM-dd')

多表关系

一对一

一对多

多对多

索引

创建一张表，插入1000000条测试数据，进行全表扫描看耗时：约0.036s

CREATE TABLE cloud_files
(
    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    file_name  TEXT NOT NULL,
    file_type  VARCHAR(20),
    created_at TIMESTAMPTZ DEFAULT NOW()
);
INSERT INTO cloud_files (file_name, file_type)
SELECT 'file_' || i || '.pdf',
       (ARRAY ['video', 'image', 'doc'])[floor(random() * 3) + 1]
FROM generate_series(1, 1000000) s(i);

SELECT *
FROM cloud_files
WHERE file_name = 'file_999999.pdf';

创建索引再看其耗时：约0.004s

CREATE INDEX idx_file_name ON cloud_files (file_name);

其他类型的索引

-- 显式指定或默认不写都是 B-Tree
CREATE INDEX idx_file_name ON cloud_files USING btree (file_name);

-- 仅适用于 = 查询
CREATE INDEX idx_file_hash ON cloud_files USING hash (file_name);

-- 注意字段顺序：(user_id, file_type) 遵循最左匹配原则
CREATE INDEX idx_user_file_type ON cloud_files (user_id, file_type);

-- 如果你的网盘支持“标签查询”或者存储了 JSON 格式的元数据，GIN 索引是神器。
-- 假设有一个 tags 字段（类型为 TEXT[]）
CREATE INDEX idx_files_tags ON cloud_files USING gin (tags);

-- 假设有一个 meta_data 字段（类型为 JSONB）
CREATE INDEX idx_files_meta ON cloud_files USING gin (meta_data jsonb_path_ops);

-- 唯一索引
CREATE UNIQUE INDEX idx_file_md5 ON cloud_files (file_md5);

这些情况下索引会“失效”

失效场景	原因说明	示例
字段参与运算 / 函数操作	索引列做计算、套函数，无法走索引	where length(file_name) = 10、where id + 1 = 100
隐式类型转换	字符串字段跟数字比较、类型不匹配，PG 自动转换导致失效	where phone = 13800138000（phone 是 varchar）
模糊查询左匹配通配符	% 或 _ 放开头，无法走普通 B 树索引	where file_name like '%.pdf'
使用 or 连接无索引字段	or 后面字段没建索引，整句索引失效	where name='a' or age=20（age 无索引）
复合索引不满足最左前缀原则	联合索引没用到最左侧列，后续列无法触发索引	索引(a,b,c)，查询where b=1 and c=2
使用 is not null	大量非空数据时，优化器倾向全表扫描，不走索引	where file_name is not null
使用 != / <> / not in	反向排除条件，数据量大时易放弃索引走全表	where status != 1、where id not in (1,2,3)
in 范围过大	in 列表值太多，优化器认为全表扫描更快，放弃索引	where id in (1,2,...1000+)
数据分布极度倾斜	符合条件数据占比过高（通常超 20%），PG 觉得全表更快	表里 90% 记录 status=1，查where status=1
索引列参与排序函数 / 分组不当	order by、group by 字段没遵循索引顺序，导致失效	索引(a,b)，order by b
分区表跨分区查询无裁剪	跨多个分区且无分区键过滤，索引优势丧失	时间分区表，不带时间条件查全量数据
索引失效 / 臃肿、统计信息陈旧	索引膨胀、长时间未 vacuum，统计信息不准，优化器选错计划	大表长期增删改、未做 Analyze

事务 & 锁

CTE & 视图

CTE也就是相当于给原本的嵌套子查询做了一层抽离，如下代码：把一条特定的SQL结果作为一个变量进行存起来，后面就可以直接用这个变量

WITH user_age_stats AS (SELECT *
                        FROM users
                        where age > 18)

SELECT *
FROM user_age_stats
WHERE sex = '男'

视图是一个虚拟表

CREATE VIEW user_age_stats_view AS
SELECT *
FROM users
where age > 18

SELECT *
FROM user_age_stats_view
WHERE sex = '男';

CTE和视图的对比

对比维度	CTE（公共表表达式）	视图（View）
定义	临时一次性结果集，WITH ... AS 定义	存储在数据库中的虚拟表，永久保存定义
生命周期	仅当前单条 SQL 执行内有效，执行完立即销毁	创建后永久存在，手动删除才失效
存储方式	不存数据，仅临时逻辑，实时计算	不存物理数据，只存 SQL 定义，查询时实时执行
复用性	只能当前 SQL 内复用，其他语句不能用	全局可复用，任意 SQL、程序都能直接调用
能否加索引	不能，临时结果集无法建索引	不能，视图是虚拟表；可给底层基表建索引
适用场景	递归查询、拆分复杂 SQL、层级树形结构、简化子查询	封装常用复杂查询、统一口径、权限控制、简化开发
可修改性	仅查询，一般不用于增删改	可更新视图（满足条件），能通过视图改基表数据
依赖关系	无依赖，随写随用	依赖底层基表，基表删了视图会失效
性能特点	每次执行重新生成结果集，适合单次复杂逻辑	每次查询都执行封装 SQL，无缓存（物化视图除外）
语法示例	WITH t AS (SELECT * FROM tb) SELECT * FROM t;	CREATE VIEW v_tb AS SELECT * FROM tb;

用户 & 角色

PostgreSQL 里：用户本质就是一种带登录权限的角色

• 角色（Role）：默认不能登录，用来做权限分组、授权
• 用户（User）：就是可以登录的角色，专门给人 / 程序登录用

创建用户&角色

CREATE ROLE managers;

CREATE USER modify WITH PASSWORD '123456';

GRANT managers TO modify;

查看有哪些角色

SELECT rolname     AS 角色名,
       rolcanlogin AS 能否登录,
       rolsuper    AS 是否超级管理员,
       rolcreatedb AS 能否建库
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%';

只读权限

CREATE USER reader_user WITH PASSWORD '123456';

GRANT CONNECT ON DATABASE db TO reader_user; -- 可以进入db这个库
GRANT USAGE ON SCHEMA public TO reader_user;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO reader_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO reader_user;

增删改查权限

-- 针对表：赋予全部增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO feng_app;

-- 针对序列：必须给权限！否则无法执行自增 ID 的插入
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO feng_app;

-- 后续新创建的表也拥有权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO feng_app;

备份 & 恢复

pg_dump单库备份

• -U: 指定用户名（通常用超级用户 postgres）
• -t: 只备份某张表（例如：-t cloud_files）
• -n: 只备份某个模式（例如：-n storage）
• -F: 指定格式。-F c 会生成自定义二进制压缩格式，体积更小，且支持并行恢复（推荐！）

备份为SQL

-- 格式：pg_dump -U [用户名] -d [数据库名] > [备份文件名.sql]
pg_dump -U postgres -d db > db_0512.sql

备份为二进制文件

-- 备份为压缩的二进制格式
pg_dump -U postgres -F c -d db > db_0512.bak

从SQL文件恢复

-- 格式：psql -U [用户名] -d [目标数据库] < [备份文件]
psql -U postgres -d db1 < db_0512.sql

从二进制文件恢复

pg_restore -U postgres -d db2 -c db_0512.bak

pg_dumpall全库备份

• -f: 代表 file，即指定要运行的 SQL 脚本文件
• postgres (最后的参数)：这是连接的初始数据库。因为pg_dumpall脚本内部包含了CREATE DATABASE和\c(切换库) 的命令，所以你只需要先连上一个默认存在的库（比如postgres），它会自动帮你创建并切换到其他的库

全库备份

pg_dumpall -U postgres > all_databases_backup.sql

恢复

-- 格式：psql -U [用户名] -f [备份文件路径] [数据库名]
psql -U postgres -f all_databases_backup.sql postgres

全文搜索

查看有哪些扩展和已经安装了那些扩展

SELECT *
FROM pg_available_extensions;

SELECT *
FROM pg_extension;

英文全文检索案例：

-- 场景：搜索包含 "PostgreSQL" 或 "Guide" 的文章
-- 使用 'english' 分词配置
SELECT title, content
FROM posts
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'PostgreSQL | Guide');

• to_tsvector：它会把一段话拆成一个个“词元”，并去掉“the”、“is”这种没意义的虚词
• @@：这是全文搜索的操作符，表示“匹配”
• &（与）、|（或）、!（非）：可以在查询中使用逻辑判断

地理位置

向量数据库