PostgreSQL 从入门到精通
一、PostgreSQL 简介
PostgreSQL 是一款功能强大的开源关系型数据库管理系统,以其稳定性、可扩展性和标准兼容性著称。它不仅仅是一个传统的关系数据库,还支持 JSON、数组、全文检索、地理信息等丰富数据类型,被誉为”世界上最先进的开源数据库”。
1.1 核心特点
| 特性 | 说明 |
|---|---|
| ACID 合规 | 完整支持事务的原子性、一致性、隔离性、持久性 |
| MVCC 多版本并发控制 | 读写互不阻塞,高并发性能优异 |
| 丰富的数据类型 | JSON/JSONB、数组、UUID、枚举、几何类型、网络地址等 |
| 强大的扩展性 | 支持自定义函数、自定义类型、存储过程(PL/pgSQL) |
| SQL 标准兼容 | 高度遵循 SQL 标准,语法规范统一 |
| 全文检索 | 内置全文搜索引擎,支持中文分词 |
1.2 PostgreSQL vs MySQL
作为 Java 开发者,你可能熟悉 MySQL。以下是两者在开发视角的对比:
| 对比维度 | PostgreSQL | MySQL |
|---|---|---|
| SQL 标准 | 更严格遵循 SQL 标准 | 有一些扩展和简化 |
| 事务隔离 | 默认 Read Committed,支持 Serializable | 默认 Repeatable Read(InnoDB) |
| JSON 支持 | JSONB 类型,支持索引和丰富操作符 | JSON 类型,操作能力相对有限 |
| 复杂查询 | 优化器更强,复杂 JOIN 性能优异 | 简单查询场景性能好 |
| 存储过程 | PL/pgSQL 功能强大 | 仅 InnoDB 支持,功能较简单 |
| 自增主键 | SERIAL / GENERATED ALWAYS AS IDENTITY | AUTO_INCREMENT |
| 数据类型 | 更丰富(数组、范围类型、几何类型等) | 基础类型够用 |
选型建议:如果你的业务需要复杂查询、JSON 存储、数据分析或地理信息处理,PostgreSQL 是更好的选择。如果是简单的 CRUD Web 应用,MySQL 也完全够用。
二、PostgreSQL 18 安装
PostgreSQL 18 是 2025 年发布的最新主要版本。以下分别介绍在 Linux(Ubuntu)和 Windows 上的安装方式。
2.1 Linux(Ubuntu 24.04)安装
2.1.1 添加官方 apt 仓库
PostgreSQL 官方提供了 apt 仓库,可以安装最新版本:
# 安装必要的依赖
sudo apt install -y curl ca-certificates
# 添加 PostgreSQL 官方 GPG 密钥
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
# 添加 apt 仓库
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# 更新包索引
sudo apt update
2.1.2 安装 PostgreSQL 18
# 安装 PostgreSQL 18
sudo apt install -y postgresql-18
# 安装后,PostgreSQL 会自动创建一个名为 postgres 的系统用户和数据库超级用户
安装完成后,PostgreSQL 服务会自动启动:
# 查看服务状态
sudo systemctl status postgresql
# 验证版本
psql --version
# 输出示例: psql (PostgreSQL) 18.0
2.1.3 初次登录与基本配置
PostgreSQL 安装后默认只允许本地连接,使用 peer 认证(系统用户映射数据库用户):
# 切换到 postgres 用户
sudo -i -u postgres
# 进入 PostgreSQL 交互终端
psql
进入 psql 后,执行以下基本配置:
-- 修改 postgres 超级用户密码
ALTER USER postgres WITH PASSWORD 'YourStrongPassword123';
-- 创建一个新的数据库用户(开发用)
CREATE USER devuser WITH PASSWORD 'DevPassword123';
-- 创建数据库并指定所有者
CREATE DATABASE mydb OWNER devuser;
-- 赋予用户数据库权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO devuser;
-- 退出
\q
2.1.4 配置远程访问(供 Navicat 等工具连接)
修改 pg_hba.conf:
sudo nano /etc/postgresql/18/main/pg_hba.conf
在文件末尾添加允许远程连接的规则:
# TYPE DATABASE USER ADDRESS METHOD
host all all 0.0.0.0/0 scram-sha-256
host all all ::/0 scram-sha-256
修改 postgresql.conf:
sudo nano /etc/postgresql/18/main/postgresql.conf
找到 listen_addresses 并修改:
listen_addresses = '*'
port = 5432
重启服务使配置生效:
sudo systemctl restart postgresql
云服务器注意:如果你使用的是腾讯云或阿里云等云服务器,还需要在安全组中放行 5432 端口。
2.2 Windows 安装
2.2.1 下载安装包
- 访问 PostgreSQL 官方下载页面:https://www.postgresql.org/download/windows/
- 下载 EnterpriseDB 提供的 PostgreSQL 18 安装包(
.exe)
如果官方页面尚未提供 PG 18 的 Windows 安装包,也可使用 PostgreSQL 的交互式安装程序 EDB,地址:https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
2.2.2 运行安装向导
- 双击运行下载的
.exe安装程序 - 安装目录:默认
C:\Program Files\PostgreSQL\18,可自定义 - 组件选择:勾选全部组件
- PostgreSQL Server(数据库服务端)
- pgAdmin 4(官方图形管理工具)
- Stack Builder(扩展安装工具)
- Command Line Tools(命令行工具)
- 数据目录:默认
C:\Program Files\PostgreSQL\18\data - 超级用户密码:为
postgres用户设置密码(请牢记,后续连接必须使用) - 端口:默认
5432,一般保持默认 - 区域设置:选择
Default locale即可 - 点击 Finish 完成安装
2.2.3 验证安装
安装完成后,PostgreSQL 会注册为 Windows 服务并自动启动:
# 在 PowerShell 中验证
psql --version
# 输出示例: psql (PostgreSQL) 18.0
# 连接数据库(输入安装时设置的密码)
psql -U postgres -h localhost
也可以打开 pgAdmin 4(开始菜单 → PostgreSQL 18 → pgAdmin 4),通过图形界面管理数据库。
2.2.4 Windows 下的服务管理
# 查看服务状态
Get-Service postgresql-x64-18
# 启动服务
Start-Service postgresql-x64-18
# 停止服务
Stop-Service postgresql-x64-18
# 重启服务
Restart-Service postgresql-x64-18
Windows 安装默认监听 localhost:5432,如果需要允许远程连接,同样需要修改 pg_hba.conf 和 postgresql.conf。配置文件路径为:
C:\Program Files\PostgreSQL\18\data\pg_hba.conf
C:\Program Files\PostgreSQL\18\data\postgresql.conf
修改后需重启 PostgreSQL 服务。
2.2.5 Windows 环境变量配置(可选)
如果希望在任意目录下使用 psql 命令,需要将 PostgreSQL 的 bin 目录添加到系统 PATH:
系统属性 → 高级 → 环境变量 → Path → 新建
C:\Program Files\PostgreSQL\18\bin
三、通过 Navicat 连接 PostgreSQL
3.1 新建连接
打开 Navicat,点击「连接」→「PostgreSQL」,填写连接信息:
连接 Linux 服务器上的 PostgreSQL
| 参数 | 值 | 说明 |
|---|---|---|
| 连接名 | PostgreSQL-Linux | 自定义名称 |
| 主机 | 110.42.213.132 | 服务器 IP |
| 端口 | 5432 | 默认端口 |
| 初始数据库 | mydb | 要连接的数据库名 |
| 用户名 | devuser | 数据库用户 |
| 密码 | DevPassword123 | 用户密码 |
连接 Windows 本地的 PostgreSQL
| 参数 | 值 | 说明 |
|---|---|---|
| 连接名 | PostgreSQL-Windows | 自定义名称 |
| 主机 | localhost | 本机地址 |
| 端口 | 5432 | 默认端口 |
| 初始数据库 | postgres | 默认维护数据库 |
| 用户名 | postgres | 超级用户 |
| 密码 | 安装时设置的密码 | 安装向导中填写的密码 |
3.2 测试连接
点击「测试连接」按钮,如果返回”连接成功”,说明配置正确。
3.3 常见连接问题排查
| 问题 | 原因 | 解决方案 |
|---|---|---|
connection refused |
服务未启动或端口未监听 | 检查 PostgreSQL 服务状态和 listen_addresses |
no pg_hba.conf entry |
客户端 IP 未被允许 | 在 pg_hba.conf 中添加客户端 IP 规则 |
password authentication failed |
密码错误或认证方式不匹配 | 检查用户密码和 pg_hba.conf 中的 METHOD |
database does not exist |
初始数据库不存在 | 确认数据库名拼写正确,或留空不指定 |
timeout |
防火墙/安全组未放行 | 检查云服务器安全组 5432 端口规则 |
3.4 Navicat 常用操作
连接成功后,你可以在 Navicat 中执行以下操作:
- 浏览数据:双击表名 → 点击「数据」标签页
- 执行 SQL:点击「查询」→「新建查询」→ 编写 SQL → 点击运行
- 表设计:双击表名 → 点击「设计」标签页可视化修改结构
- 数据导出:右键表 →「数据传输」/「导出向导」
四、SQL 概述
SQL(Structured Query Language,结构化查询语言)是操作关系型数据库的标准语言。PostgreSQL 高度遵循 SQL 标准,同时在此基础上有许多增强扩展。
4.1 SQL 语句分类
| 分类 | 全称 | 说明 | 关键字 |
|---|---|---|---|
| DDL | Data Definition Language | 数据定义语言,定义和修改数据库结构 | CREATE、ALTER、DROP |
| DML | Data Manipulation Language | 数据操作语言,对数据进行增删改 | INSERT、UPDATE、DELETE |
| DQL | Data Query Language | 数据查询语言,检索数据 | SELECT |
| DCL | Data Control Language | 数据控制语言,管理权限 | GRANT、REVOKE |
| TCL | Transaction Control Language | 事务控制语言,管理事务 | BEGIN、COMMIT、ROLLBACK |
4.2 SQL 书写规范
-- 1. 关键字大写,表名和列名小写(PostgreSQL 默认大小写敏感,未加引号的标识符被转为小写)
-- 2. 语句以分号结尾
-- 3. 使用 -- 单行注释,/* */ 多行注释
-- 4. 合理使用缩进和换行,提高可读性
SELECT
u.username,
u.email,
COUNT(a.id) AS article_count
FROM users u
LEFT JOIN articles a ON u.id = a.user_id -- 左连接
WHERE u.status = 1
GROUP BY u.username, u.email
ORDER BY article_count DESC;
4.3 标识符与引号规则
PostgreSQL 的标识符规则是初学者常踩的坑:
-- 未加引号的标识符:自动转为小写
CREATE TABLE MyTable (id INT); -- 实际创建的是 mytable
SELECT * FROM MyTable; -- 实际查的是 mytable ✅
SELECT * FROM mytable; -- ✅
-- 加双引号的标识符:大小写敏感,原样保留
CREATE TABLE "MyTable" (id INT); -- 创建的是 MyTable(注意大小写)
SELECT * FROM MyTable; -- ❌ 报错:relation "mytable" does not exist
SELECT * FROM "MyTable"; -- ✅ 需要加双引号
-- 建议:始终用小写 + 下划线命名,不加双引号
CREATE TABLE my_table (id INT); -- ✅ 最佳实践
单引号 vs 双引号:单引号
'...'用于字符串字面量,双引号"..."用于标识符(表名、列名)。这是 SQL 标准的规定。
4.4 字符串与数值字面量
-- 字符串:用单引号包裹
SELECT 'Hello World';
SELECT 'I''m a developer'; -- 单引号内转义用两个单引号
-- 数值:直接书写
SELECT 42; -- 整数
SELECT 3.14; -- 浮点数
SELECT 1.5e3; -- 科学计数法 = 1500.0
-- 布尔值
SELECT TRUE, FALSE, NULL;
-- 日期时间
SELECT DATE '2025-01-15';
SELECT TIMESTAMP '2025-01-15 14:30:00';
五、数据库操作
PostgreSQL 的数据组织结构是三级层次:数据库(Database)→ 模式(Schema)→ 表(Table)。理解这个结构是正确使用 PostgreSQL 的基础。
5.1 数据库、模式与表的关系
PostgreSQL 实例(Instance)
│
├── postgres ← 默认维护数据库
├── mydb ← 业务数据库 A
│ ├── public ← Schema(默认模式)
│ │ ├── users ← 表
│ │ ├── orders
│ │ └── ...
│ ├── ecommerce ← Schema(自定义模式)
│ │ ├── products
│ │ └── order_items
│ └── ...
├── testdb ← 业务数据库 B
│ └── public
│ └── ...
└── template1 ← 模板数据库(创建新数据库时复制它)
5.1.1 数据库(Database)
数据库是 PostgreSQL 中最顶层的组织单元。 一个 PostgreSQL 实例可以包含多个数据库,数据库之间完全隔离——你不能在一个数据库的查询中直接访问另一个数据库的表。
| 特性 | 说明 |
|---|---|
| 隔离性 | 数据库之间相互独立,无法跨库查询(需借助 dblink 或 postgres_fdw 扩展) |
| 模板机制 | 创建新数据库时基于 template1(默认)或 template0 复制 |
| 编码与排序 | 每个数据库可指定独立的字符编码(UTF8)和区域排序规则 |
| 所有者 | 每个数据库有一个所有者(通常是创建者),只有所有者或超级用户可删除 |
5.1.2 模式(Schema)
模式是数据库内部的命名空间, 用于在同一个数据库中将表、视图、函数等对象进行逻辑分组。PostgreSQL 每个数据库默认有一个名为 public 的模式。
| 特性 | 说明 |
|---|---|
| 命名空间 | 同一个模式下不能有同名表,但不同模式可以 |
| 搜索路径 | 通过 search_path 决定查找对象时依次搜索哪些模式(类似 Java 的 import) |
| 权限控制 | 可以对模式单独授权,控制用户对表组的访问 |
| 典型用途 | 多团队共享数据库时按业务模块隔离;或按版本隔离(如 v1、v2) |
数据库 vs 模式的选择:
- 数据隔离级别要求高、不需要跨模块联合查询 → 使用不同数据库
- 需要跨模块联合查询、但想逻辑分组 → 使用不同模式
5.1.3 表(Table)
表是数据库中存储数据的基本单元, 属于某个模式。表由列(字段)和行(记录)组成,每列有明确的数据类型和约束。
-- 完整的对象引用路径(模式名.表名)
SELECT * FROM ecommerce.products;
-- 如果 search_path 包含 ecommerce,可省略模式名
SET search_path TO ecommerce, public;
SELECT * FROM products; -- 先在 ecommerce 找,找不到再去 public
5.2 数据库操作
-- ==================== 创建数据库 ====================
-- 基本创建
CREATE DATABASE mydb;
-- 完整语法:指定所有者、编码、模板
CREATE DATABASE mydb
OWNER devuser -- 指定数据库所有者
ENCODING 'UTF8' -- 字符编码
LC_COLLATE 'en_US.UTF-8' -- 字符串排序规则
LC_CTYPE 'en_US.UTF-8' -- 字符分类规则
TEMPLATE template0; -- 使用的模板数据库
-- ==================== 查看数据库 ====================
-- psql 快捷命令
\l -- 列出所有数据库
\l+ -- 显示详细信息(含大小、表空间)
-- SQL 方式
SELECT datname, pg_get_userbyid(datdba) AS owner, encoding
FROM pg_database WHERE datistemplate = false;
-- 查看当前所在数据库
SELECT current_database();
-- ==================== 切换数据库 ====================
-- psql 命令(SQL 中无法直接切换数据库)
\c mydb -- 切换到 mydb
\c mydb devuser -- 以 devuser 身份连接到 mydb
\c - devuser -- 连接到当前数据库,但切换用户
-- ==================== 修改数据库 ====================
-- 重命名数据库
ALTER DATABASE mydb RENAME TO newdb;
-- 修改所有者
ALTER DATABASE mydb OWNER TO newuser;
-- 设置连接参数(如限制连接数)
ALTER DATABASE mydb CONNECTION LIMIT 100;
-- 为数据库设置默认参数(所有新连接生效)
ALTER DATABASE mydb SET log_min_duration_statement TO 300;
-- ==================== 删除数据库 ====================
DROP DATABASE mydb; -- 删除(必须没有活动连接)
DROP DATABASE IF EXISTS mydb; -- 存在才删除(避免报错)
-- 如果有活动连接,需先断开:
-- SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='mydb';
注意事项:
- 创建/删除数据库操作不能在事务块中执行
DROP DATABASE不可回滚,执行前务必确认- 不能删除当前正在连接的数据库,需先
\c切换到其他数据库
5.3 模式操作
-- ==================== 创建模式 ====================
CREATE SCHEMA ecommerce;
-- 指定所有者
CREATE SCHEMA ecommerce AUTHORIZATION devuser;
-- ==================== 查看模式 ====================
-- 查看当前数据库下的所有模式
SELECT schema_name FROM information_schema.schemata;
-- psql 快捷命令
\dn -- 列出所有模式
\dn+ -- 显示详细信息
-- ==================== 搜索路径 ====================
-- 查看当前搜索路径
SHOW search_path;
-- 默认结果: "$user", public
-- 设置搜索路径(仅当前会话有效)
SET search_path TO ecommerce, public;
-- 设置搜索路径(永久生效)
ALTER ROLE devuser SET search_path TO ecommerce, public;
-- ==================== 删除模式 ====================
DROP SCHEMA ecommerce; -- 模式必须为空
DROP SCHEMA ecommerce CASCADE; -- 连同模式下的所有对象一起删除
-- ==================== 模式应用示例 ====================
-- 场景:电商系统中,将商品和订单分属不同模式
CREATE SCHEMA catalog; -- 商品目录
CREATE SCHEMA sales; -- 销售订单
-- 在 catalog 模式下建表
CREATE TABLE catalog.products (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price NUMERIC(10,2) NOT NULL
);
-- 在 sales 模式下建表,外键引用 catalog 模式的表
CREATE TABLE sales.orders (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL CHECK (quantity > 0),
FOREIGN KEY (product_id) REFERENCES catalog.products(id)
);
-- 跨模式联合查询
SET search_path TO catalog, sales;
SELECT p.name, o.quantity, p.price * o.quantity AS total
FROM products p
JOIN orders o ON p.id = o.product_id;
5.4 常用 psql 元命令速查
| 命令 | 作用 |
|---|---|
\l |
列出所有数据库 |
\c 库名 |
切换数据库 |
\dn |
列出所有模式 |
\dt |
列出当前模式下所有表 |
\dt 模式名.* |
列出指定模式下的所有表 |
\d 表名 |
查看表结构(列、类型、约束、索引等) |
\d+ 表名 |
查看表结构详细信息 |
\du |
列出所有用户/角色 |
\db |
列出所有表空间 |
\dx |
列出已安装的扩展 |
\x |
切换扩展显示模式(表格→纵向,宽表时有用) |
\q |
退出 psql |
六、表与数据类型
6.1 建表语法
PostgreSQL 建表使用 CREATE TABLE 语句,完整语法结构如下:
CREATE TABLE [IF NOT EXISTS] [模式名.]表名 (
-- 列定义
列名 数据类型 [列约束],
列名 数据类型 [列约束],
...
-- 表级约束(当约束涉及多列时必须写在表级)
[CONSTRAINT 约束名] 约束类型 (列名, ...),
...
);
-- IF NOT EXISTS: 表已存在时不报错(只给提示)
-- 模式名: 省略时使用 search_path 中的第一个模式
基本示例:
-- 创建用户表
CREATE TABLE users (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
nickname VARCHAR(50),
age INTEGER,
status SMALLINT DEFAULT 1,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
带表级约束的建表:
CREATE TABLE articles (
id INTEGER GENERATED ALWAYS AS IDENTITY,
user_id INTEGER NOT NULL,
title VARCHAR(200) NOT NULL,
content TEXT,
view_count INTEGER DEFAULT 0,
is_published BOOLEAN DEFAULT FALSE,
published_at TIMESTAMPTZ,
-- 表级主键约束
CONSTRAINT pk_articles PRIMARY KEY (id),
-- 表级唯一约束
CONSTRAINT uk_articles_title UNIQUE (title),
-- 表级外键约束
CONSTRAINT fk_articles_user FOREIGN KEY (user_id)
REFERENCES users(id) ON DELETE CASCADE,
-- 表级检查约束
CONSTRAINT chk_view_count CHECK (view_count >= 0)
);
6.2 命名规范
在正式项目中,使用统一的命名规范可以提高代码可读性和可维护性:
| 对象 | 规范 | 正确示例 | 错误示例 |
|---|---|---|---|
| 表名 | 小写 + 下划线,名词复数 | users、order_items |
Users、OrderItems、T_USER |
| 列名 | 小写 + 下划线 | user_id、created_at |
userId、CreateTime |
| 主键约束 | pk_表名 |
pk_users |
primary_key_users |
| 唯一约束 | uk_表名_字段 或 uq_表名_字段 |
uk_users_email |
unique_email |
| 外键约束 | fk_从表_主表 或 fk_表名_字段 |
fk_articles_user |
fk_user |
| 检查约束 | chk_表名_字段 或 chk_表名_规则 |
chk_users_age |
check_age |
| 默认值约束 | 通常由列本身承担,无需命名 | — | — |
| 索引 | idx_表名_字段1[_字段2] |
idx_articles_user_id |
index1 |
| 模式名 | 小写,业务模块名 | catalog、sales |
Catalog |
关键规则:
- PostgreSQL 默认将未加引号的标识符转为小写,
"MyTable"和mytable是两个不同的表,建议始终用小写加下划线- 关键字(
SELECT、CREATE等)大写,表名列名小写,便于区分- 约束加显式名字(
CONSTRAINT 约束名 ...),否则 PostgreSQL 会自动生成users_email_key这样难以管理的名字
6.3 数据类型
PostgreSQL 拥有所有主流数据库中最丰富的数据类型体系。
6.3.1 数值类型
| 类型 | 说明 | 范围 | 典型用途 |
|---|---|---|---|
SMALLINT |
小整数 | -32,768 ~ 32,767 | 枚举状态、排序号 |
INTEGER / INT |
整数 | ±21 亿 | 一般整数、外键 |
BIGINT |
大整数 | ±9.2 × 10¹⁸ | 自增主键、大数据量ID |
DECIMAL(p,s) |
精确小数 | 精度可指定 | 金额、科学计算 |
NUMERIC(p,s) |
同 DECIMAL | 精度可指定 | 同上,两者完全等价 |
REAL |
单精度浮点 | 6位有效数字 | 近似计算 |
DOUBLE PRECISION |
双精度浮点 | 15位有效数字 | 科学计算 |
SERIAL |
自增整数(旧语法) | 等同 INT + 序列 | 兼容旧代码 |
BIGSERIAL |
自增大整数(旧语法) | 等同 BIGINT + 序列 | 兼容旧代码 |
p= 总位数(精度),s= 小数位数(标度)。 如NUMERIC(10,2)表示最多 8 位整数 + 2 位小数。金额必须用
NUMERIC/DECIMAL,不能用浮点数! 浮点数在二进制中无法精确表示十进制小数,会导致精度丢失。
PostgreSQL 18 推荐使用 GENERATED ... AS IDENTITY 替代 SERIAL(见 6.4 节)。
6.3.2 字符串类型
| 类型 | 说明 | 最大长度 |
|---|---|---|
CHAR(n) / CHARACTER(n) |
定长字符串,不足补空格 | n 个字符 |
VARCHAR(n) / CHARACTER VARYING(n) |
变长字符串,有上限 | n 个字符 |
TEXT |
变长字符串,无上限 | 无限制 |
开发建议: PostgreSQL 中
VARCHAR(n)和TEXT底层存储机制完全相同(都是变长存储),VARCHAR(n)只是多了一个长度检查约束。不确定性大时直接用TEXT即可,明确知道上限时用VARCHAR(n)。
6.3.3 日期时间类型
| 类型 | 说明 | 示例 | 注意事项 |
|---|---|---|---|
DATE |
仅日期 | 2025-01-15 |
无时间部分 |
TIME |
仅时间 | 14:30:00 |
无日期部分 |
TIMESTAMP |
日期+时间 | 2025-01-15 14:30:00 |
不带时区 |
TIMESTAMPTZ |
带时区的时间戳 | 2025-01-15 14:30:00+08 |
推荐,自动转换时区 |
INTERVAL |
时间间隔 | INTERVAL '1 day' |
用于时间计算 |
BYTEA |
二进制数据 | — | 存储图片、文件等 |
推荐使用
TIMESTAMPTZ而非TIMESTAMP。TIMESTAMPTZ内部以 UTC 存储,查询时自动按客户端时区显示,避免跨时区的时间混乱。
6.3.4 布尔与特殊类型
| 类型 | 说明 | 取值 |
|---|---|---|
BOOLEAN |
布尔值 | TRUE / FALSE / NULL |
UUID |
通用唯一标识符 | gen_random_uuid() 生成 |
JSON |
JSON 文本(保留原始格式) | '{"key":"value"}' |
JSONB |
二进制 JSON(支持索引,推荐) | '{"key":"value"}' |
ARRAY |
数组类型 | INTEGER[]、TEXT[] |
ENUM |
枚举类型 | 自定义值集合 |
INET / CIDR |
IP 地址 / 网段 | 192.168.1.1 / 192.168.0.0/16 |
XML |
XML 数据 | — |
6.3.5 JSON 与 JSONB
-- JSON: 存储为原始文本,保留输入格式(空格、键顺序)
-- JSONB: 存储为解析后的二进制格式,可索引,查询更快但会丢失格式
-- 开发中推荐 JSONB
-- JSONB 操作示例
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title VARCHAR(200),
metadata JSONB
);
-- 插入 JSONB 数据
INSERT INTO articles (title, metadata) VALUES
('PostgreSQL 教程', '{"author": "Dailyblue", "tags": ["数据库", "PG"], "views": 1000}'),
('Java 并发', '{"author": "张三", "tags": ["Java", "多线程"], "views": 500}');
-- 查询 JSONB 字段
-- ->> 返回文本类型值
-- -> 返回 JSON 类型值
SELECT title,
metadata->>'author' AS author,
metadata->'tags' AS tags
FROM articles;
-- 条件查询 JSONB
SELECT * FROM articles
WHERE metadata->>'author' = 'Dailyblue';
-- 包含查询(判断 JSONB 中是否包含指定结构)
SELECT * FROM articles
WHERE metadata @> '{"tags": ["数据库"]}';
-- 使用 GIN 索引加速 JSONB 查询
CREATE INDEX idx_articles_metadata ON articles USING gin(metadata);
6.4 约束
约束用于保证数据的完整性和一致性。PostgreSQL 支持以下约束类型:
| 约束 | 关键字 | 作用 | 列级/表级 |
|---|---|---|---|
| 主键 | PRIMARY KEY |
唯一且非空,每表只能一个 | 均可 |
| 外键 | FOREIGN KEY |
引用其他表的主键/唯一键 | 均可 |
| 唯一 | UNIQUE |
值不重复,但允许 NULL | 均可 |
| 非空 | NOT NULL |
不允许 NULL | 仅列级 |
| 检查 | CHECK |
满足自定义条件 | 均可 |
| 默认值 | DEFAULT |
插入时未指定则使用默认值 | 仅列级 |
6.4.1 主键约束
-- 列级写法(单列主键)
CREATE TABLE users (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
...
);
-- 表级写法(单列主键,推荐命名约束)
CREATE TABLE users (
id INTEGER GENERATED ALWAYS AS IDENTITY,
...
CONSTRAINT pk_users PRIMARY KEY (id)
);
-- 复合主键(必须表级写法)
CREATE TABLE order_items (
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id)
);
6.4.2 外键约束
-- 列级写法(简写)
CREATE TABLE articles (
...
user_id INTEGER REFERENCES users(id),
...
);
-- 表级写法(推荐,可命名 + 指定删除/更新行为)
CREATE TABLE articles (
...
user_id INTEGER NOT NULL,
CONSTRAINT fk_articles_user FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE -- 删除用户时,级联删除其文章
ON UPDATE NO ACTION -- 更新用户 id 时不触发任何动作(默认)
);
-- 外键的 ON DELETE / ON UPDATE 选项:
-- CASCADE : 级联删除/更新
-- RESTRICT : 阻止删除(有引用时报错)
-- SET NULL : 将外键设为 NULL
-- SET DEFAULT : 将外键设为默认值
-- NO ACTION : 同 RESTRICT,但检查延迟到事务末尾(默认行为)
RESTRICT和NO ACTION的区别:RESTRICT立即检查,NO ACTION在事务结束时检查。如果外键约束是DEFERRABLE的,NO ACTION可以在事务内先删被引用行再删引用行而不报错。
6.4.3 唯一约束
-- 列级写法
CREATE TABLE users (
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE,
...
);
-- 表级写法(推荐命名)
CREATE TABLE users (
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
...
CONSTRAINT uk_users_username UNIQUE (username),
CONSTRAINT uk_users_email UNIQUE (email)
);
-- 复合唯一约束
CREATE TABLE user_roles (
user_id INTEGER NOT NULL,
role_id INTEGER NOT NULL,
CONSTRAINT uk_user_roles UNIQUE (user_id, role_id)
);
UNIQUE 允许 NULL:多行 NULL 值不会违反唯一约束(PostgreSQL 认为每个 NULL 都”不同”)。如果需要限制不能有多行 NULL,可用
NULLS NOT DISTINCT(PostgreSQL 15+)或加NOT NULL。
6.4.4 非空约束
-- 只有列级写法
CREATE TABLE users (
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
nickname VARCHAR(50), -- 允许 NULL
...
);
-- 对已有表添加/删除非空约束
ALTER TABLE users ALTER COLUMN nickname SET NOT NULL;
ALTER TABLE users ALTER COLUMN nickname DROP NOT NULL;
6.4.5 检查约束
-- 列级写法(简单条件)
CREATE TABLE users (
...
age INTEGER CHECK (age >= 0 AND age <= 150),
status SMALLINT CHECK (status IN (0, 1, 2)),
...
);
-- 表级写法(可命名 + 可引用多列)
CREATE TABLE orders (
...
start_date DATE NOT NULL,
end_date DATE,
amount NUMERIC(10,2) NOT NULL,
status VARCHAR(20) DEFAULT 'pending',
CONSTRAINT chk_amount CHECK (amount > 0),
CONSTRAINT chk_status CHECK (status IN ('pending', 'paid', 'shipped', 'completed', 'cancelled')),
CONSTRAINT chk_date_range CHECK (end_date IS NULL OR end_date >= start_date)
);
6.4.6 默认值约束
-- 列级写法
CREATE TABLE users (
...
status SMALLINT DEFAULT 1,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
...
);
-- 使用函数作为默认值
CREATE TABLE sessions (
id UUID DEFAULT gen_random_uuid(),
token VARCHAR(64) DEFAULT encode(gen_random_bytes(32), 'hex'),
expires_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP + INTERVAL '24 hours'
);
6.4.7 自增主键
PostgreSQL 18 推荐使用 GENERATED ... AS IDENTITY 替代 SERIAL:
-- 新写法(SQL 标准兼容,推荐)
CREATE TABLE products (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(200) NOT NULL
);
-- GENERATED ALWAYS: 阻止手动插入 id(更安全)
-- GENERATED BY DEFAULT: 允许手动指定 id(兼容 SERIAL 行为)
-- 序列相关操作
-- 查看当前序列值
SELECT currval(pg_get_serial_sequence('products', 'id'));
-- 重置序列值
ALTER TABLE products ALTER COLUMN id RESTART WITH 1000;
SERIAL的本质是创建一个序列 + 设置默认值为nextval(序列),属于 PostgreSQL 特有语法。GENERATED AS IDENTITY是 SQL 标准,在其他数据库中也可移植。
6.5 表结构修改(ALTER TABLE)
-- ==================== 列操作 ====================
-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- 添加带约束的列
ALTER TABLE users ADD COLUMN email VARCHAR(100) NOT NULL DEFAULT '';
-- 删除列
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users DROP COLUMN IF EXISTS phone; -- 不存在时不报错
-- 重命名列
ALTER TABLE users RENAME COLUMN nickname TO display_name;
-- 修改列类型
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;
-- 类型不兼容时需指定 USING 子句
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT USING age::SMALLINT;
-- 修改列默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 0;
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;
-- ==================== 约束操作 ====================
-- 添加约束
ALTER TABLE users ADD CONSTRAINT chk_email CHECK (email LIKE '%@%');
ALTER TABLE users ADD CONSTRAINT uk_users_phone UNIQUE (phone);
-- 添加外键
ALTER TABLE articles
ADD CONSTRAINT fk_articles_user
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
-- 删除约束
ALTER TABLE users DROP CONSTRAINT chk_email;
ALTER TABLE users DROP CONSTRAINT IF EXISTS chk_email;
-- ==================== 表操作 ====================
-- 重命名表
ALTER TABLE users RENAME TO sys_users;
-- 移动表到其他模式
ALTER TABLE users SET SCHEMA ecommerce;
-- 删除表
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS users CASCADE; -- 连带依赖对象(如外键引用)一起删除
七、数据操作(DML – 增删改)
7.1 INSERT(插入数据)
-- 插入单条
INSERT INTO users (username, email, password, nickname, age)
VALUES ('dailyblue', 'dailyblue@example.com', 'hashed_pwd', '天天天蓝', 28);
-- 插入多条
INSERT INTO users (username, email, password, nickname, age)
VALUES
('zhangsan', 'zhangsan@example.com', 'pwd1', '张三', 25),
('lisi', 'lisi@example.com', 'pwd2', '李四', 30),
('wangwu', 'wangwu@example.com', 'pwd3', '王五', 22);
-- INSERT ... ON CONFLICT(UPSERT,PostgreSQL 特色功能)
INSERT INTO users (username, email, password)
VALUES ('dailyblue', 'new@example.com', 'new_pwd')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email, password = EXCLUDED.password;
-- ON CONFLICT (username) DO NOTHING; -- 冲突时什么都不做
-- RETURNING 返回插入的数据(PostgreSQL 特色)
INSERT INTO articles (user_id, title, content)
VALUES (1, 'PostgreSQL 入门', '正文内容...')
RETURNING id, created_at;
7.2 UPDATE(更新数据)
-- 基本更新
UPDATE users SET nickname = '蓝色天天', age = 29 WHERE username = 'dailyblue';
-- 更新多个字段
UPDATE users SET status = 2, updated_at = CURRENT_TIMESTAMP
WHERE id = 1;
-- 基于子查询更新
UPDATE articles SET view_count = view_count + 1
WHERE id IN (SELECT id FROM articles WHERE is_published = TRUE);
-- 使用 FROM 引用其他表(PostgreSQL 扩展语法)
UPDATE articles a
SET view_count = a.view_count + v.daily_views
FROM (
SELECT user_id, COUNT(*) AS daily_views
FROM article_views
WHERE view_date = CURRENT_DATE
GROUP BY user_id
) v
WHERE a.user_id = v.user_id;
-- RETURNING 返回更新后的数据
UPDATE users SET status = 0 WHERE age < 18 RETURNING id, username;
7.3 DELETE(删除数据)
-- 条件删除
DELETE FROM users WHERE status = 0 AND updated_at < NOW() - INTERVAL '1 year';
-- 使用 USING 引用其他表(PostgreSQL 扩展语法)
DELETE FROM articles
USING users
WHERE articles.user_id = users.id AND users.status = 0;
-- 删除全表数据(保留表结构,比 DELETE 快,不触发触发器)
TRUNCATE TABLE articles RESTART IDENTITY;
-- RETURNING 返回删除的数据
DELETE FROM users WHERE status = 0 RETURNING id, username;
开发建议:在执行 UPDATE/DELETE 前,先用相同 WHERE 条件的 SELECT 查一遍,确认影响范围后再执行写操作,避免误操作!
八、数据查询(DQL – SELECT 详解)
8.1 基本查询
-- 查询所有列(生产环境不推荐 SELECT *)
SELECT * FROM users;
-- 查询指定列
SELECT id, username, email, nickname FROM users;
-- 列别名
SELECT username AS 用户名, email AS 邮箱 FROM users;
-- 去重
SELECT DISTINCT status FROM users;
8.2 条件查询(WHERE)
-- 比较运算符:=, !=, <>, <, >, <=, >=
SELECT * FROM users WHERE age >= 25;
-- 逻辑运算符:AND, OR, NOT
SELECT * FROM users
WHERE age >= 25 AND status = 1
ORDER BY created_at DESC;
-- IN / NOT IN
SELECT * FROM users WHERE status IN (1, 2, 3);
-- BETWEEN
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE nickname IS NULL;
SELECT * FROM users WHERE nickname IS NOT NULL;
-- 模糊查询
SELECT * FROM users WHERE username LIKE 'd%'; -- 以 d 开头
SELECT * FROM users WHERE username ILIKE 'D%'; -- 不区分大小写
SELECT * FROM users WHERE username ~ '^d.*e$'; -- 正则表达式匹配
8.3 排序与分页
-- 排序
SELECT * FROM users ORDER BY created_at DESC; -- 降序
SELECT * FROM users ORDER BY status ASC, id DESC; -- 多字段排序
-- 分页查询(LIMIT + OFFSET)
SELECT * FROM users
ORDER BY id DESC
LIMIT 10 OFFSET 0; -- 第一页,每页 10 条
-- 分页查询(推荐:游标分页,大数据量性能更好)
SELECT * FROM users
WHERE id < 100
ORDER BY id DESC
LIMIT 10;
8.4 聚合查询
-- 聚合函数
SELECT
status,
COUNT(*) AS total,
AVG(age) AS avg_age,
MIN(age) AS min_age,
MAX(age) AS max_age,
SUM(age) AS total_age
FROM users
GROUP BY status
HAVING COUNT(*) > 0;
GROUP BY 执行顺序图解:
FROM users -- 1. 确定数据源
WHERE age > 18 -- 2. 行级过滤
GROUP BY status -- 3. 分组
HAVING COUNT(*) > 0 -- 4. 组级过滤
SELECT ... -- 5. 选择列 + 聚合
ORDER BY total DESC -- 6. 排序
LIMIT 10 -- 7. 分页
8.5 JOIN(多表关联查询)
-- 准备数据:假设有 users 和 articles 两张表
-- INNER JOIN(内连接,最常用)
SELECT
u.id AS user_id,
u.username,
a.id AS article_id,
a.title,
a.view_count
FROM users u
INNER JOIN articles a ON u.id = a.user_id
WHERE a.is_published = TRUE
ORDER BY a.view_count DESC;
-- LEFT JOIN(左连接,保留左表所有行)
SELECT
u.username,
COUNT(a.id) AS article_count
FROM users u
LEFT JOIN articles a ON u.id = a.user_id
GROUP BY u.id, u.username
ORDER BY article_count DESC;
-- RIGHT JOIN(右连接,保留右表所有行)
SELECT u.username, a.title
FROM users u
RIGHT JOIN articles a ON u.id = a.user_id;
-- FULL JOIN(全连接,保留两表所有行)
SELECT u.username, a.title
FROM users u
FULL JOIN articles a ON u.id = a.user_id;
-- 多表连接
SELECT
u.username,
a.title,
o.order_no,
o.amount
FROM users u
JOIN articles a ON u.id = a.user_id
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid';
-- 自连接(例如:查找同一用户的多篇文章)
SELECT a1.title AS title1, a2.title AS title2
FROM articles a1
JOIN articles a2 ON a1.user_id = a2.user_id AND a1.id < a2.id;
JOIN 类型对比图:
INNER JOIN LEFT JOIN RIGHT JOIN FULL JOIN
A ∩ B A 全部 + B 交集 A 交集 + B 全部 A 全部 + B 全部
┌───┐ ┌───┐ ┌───┐ ┌───┐
│ A │ │ A │ │ A │ │ A │
└─┬─┘ └─┬─┘ └─┬─┘ └─┬─┘
│ │ │ │
┌─┴─┐ ┌─┴─┐ ┌─┴─┐ ┌─┴─┐
│ B │ │B⊂ │ │ ⊃B│ │ A+B│
└───┘ └───┘ └───┘ └───┘
8.6 子查询
-- 标量子查询(返回单个值)
SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);
-- 列子查询(返回一列)
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM articles WHERE is_published = TRUE);
-- EXISTS 子查询
SELECT u.username FROM users u
WHERE EXISTS (
SELECT 1 FROM articles a WHERE a.user_id = u.id AND a.is_published = TRUE
);
-- FROM 子句中的子查询(派生表)
SELECT t.username, t.article_count
FROM (
SELECT u.username, COUNT(a.id) AS article_count
FROM users u
LEFT JOIN articles a ON u.id = a.user_id
GROUP BY u.id, u.username
) t
WHERE t.article_count > 5;
九、事务与并发控制
9.1 什么是事务
事务(Transaction) 是数据库操作的最小逻辑执行单元,由一组 SQL 语句组成。这组语句要么全部成功执行,要么全部不执行,不会出现”部分执行”的中间状态。
以银行转账为例:从账户 A 向账户 B 转 100 元,需要两步操作——A 扣款 100、B 加款 100。如果第一步成功但第二步失败,A 的钱凭空消失了。事务确保这两步要么同时成功,要么同时回滚。
9.2 ACID 详解
ACID 是事务的四大特性,是数据库可靠性的基石:
A – 原子性(Atomicity)
核心原则:要么全部成功,要么全部失败,不存在中间状态。
原子性保证一个事务中的所有操作是一个不可分割的整体。如果事务中任何一条语句失败,整个事务中已执行的操作都会被撤销(Rollback),数据库状态恢复到事务开始前的状态。
-- 转账事务
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- A 扣款
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- B 加款
-- 如果上面任何一步失败,整个事务回滚,A 和 B 的余额都不变
COMMIT;
实现原理:PostgreSQL 通过 WAL(Write-Ahead Logging,预写日志)实现原子性。事务执行过程中的修改先写入 WAL 日志,只有在 COMMIT 时才真正持久化。如果中途崩溃,恢复时根据 WAL 日志回滚未提交的事务。
C – 一致性(Consistency)
核心原则:事务执行前后,数据库始终处于合法状态。
一致性要求数据库在事务执行前和执行后都满足所有的约束规则,包括:主键约束、外键约束、唯一约束、CHECK 约束、触发器规则等。一致性是事务的最终目标,原子性、隔离性、持久性都是为了保证一致性。
-- 假设有约束:余额不能为负数
-- CONSTRAINT chk_balance CHECK (balance >= 0)
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 1; -- A 余额为 150,扣 200
-- 违反 CHECK 约束,事务自动失败
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;
-- 结果:整个事务回滚,数据库恢复到事务前的一致状态
一致性 vs 原子性:原子性保证”全做或全不做”,一致性保证”做的结果必须合法”。原子性是手段,一致性是目的。
I – 隔离性(Isolation)
核心原则:并发执行的多个事务之间互不干扰。
当多个事务同时操作相同数据时,隔离性决定了一个事务能否看到其他事务未提交的修改。隔离性通过隔离级别来控制,隔离级别越高,数据一致性越好,但并发性能越低。
四种隔离级别:
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能影响 |
|---|---|---|---|---|
| Read Uncommitted | 可能 | 可能 | 可能 | 最低 |
| Read Committed(PG 默认) | 避免 | 可能 | 可能 | 低 |
| Repeatable Read | 避免 | 避免 | 避免(PG 特有) | 中 |
| Serializable | 避免 | 避免 | 避免 | 最高 |
三种并发问题详解:
① 脏读(Dirty Read) — 事务 A 读到了事务 B 未提交的修改,如果 B 回滚,A 读到的就是”脏数据”:
时间线 事务A(读取) 事务B(写入)
T1 BEGIN;
T2 UPDATE users SET age=99 WHERE id=1;
T3 SELECT age FROM users (age=99,读到了未提交的值)
T4 ROLLBACK;(回滚!age 恢复原值)
T5 (A 拿着 age=99 做了决策,但这个值根本不存在)
PostgreSQL 中所有隔离级别都不允许脏读。
② 不可重复读(Non-Repeatable Read) — 事务 A 两次读取同一行,结果不同(因为事务 B 修改并提交了):
时间线 事务A(读取) 事务B(写入)
T1 BEGIN;
T2 SELECT age FROM users WHERE id=1; (age=25)
T3 BEGIN;
T4 UPDATE users SET age=26 WHERE id=1;
T5 COMMIT;
T6 SELECT age FROM users WHERE id=1; (age=26,变了!)
T7 COMMIT;
在 Read Committed 级别会发生,Repeatable Read 级别可以避免。
③ 幻读(Phantom Read) — 事务 A 两次执行相同查询,结果集行数不同(因为事务 B 插入/删除了新行):
时间线 事务A(统计) 事务B(插入)
T1 BEGIN;
T2 SELECT COUNT(*) FROM users WHERE age > 20; (结果=5)
T3 BEGIN;
T4 INSERT INTO users(age) VALUES(28);
T5 COMMIT;
T6 SELECT COUNT(*) FROM users WHERE age > 20; (结果=6,多了一行!)
T7 COMMIT;
PostgreSQL 的 Repeatable Read 级别使用 MVCC 快照机制,可以避免幻读(这是 PostgreSQL 的优势,SQL 标准中 RR 级别允许幻读)。
隔离级别设置:
-- 设置当前事务的隔离级别
BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- 设置会话级别
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
PostgreSQL 隔离级别的关系:
PostgreSQL 实际只实现了 3 个级别(Read Uncommitted 被当作 Read Committed 处理)
Read Uncommitted ──(等同于)──> Read Committed(PG 默认)
│
│ 避免脏读
▼
Repeatable Read
│
│ 避免不可重复读 + 幻读(PG 增强)
▼
Serializable
避免所有并发问题,但性能最低
D – 持久性(Durability)
核心原则:事务一旦提交,对数据的修改就是永久性的,即使系统崩溃也不会丢失。
持久性保证已提交事务的修改永久保存在磁盘中。PostgreSQL 通过 WAL 机制实现持久性——事务提交时,修改先写入 WAL 日志并 fsync 到磁盘,之后才返回成功。即使数据库崩溃,恢复时也能从 WAL 日志重做已提交的事务。
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT; -- 一旦返回成功,这个修改就永久生效
-- 即使此时服务器断电,重启后数据依然在
ACID 四特性的关系总结:
┌──────────────────────────────────────────────────┐
│ 一致性 (C) ── 目标 │
│ ┌──────────┬──────────┬──────────┐ │
│ │ │ │ │ │
│ 原子性(A) 隔离性(I) 持久性(D) │
│ 全做或 并发事务 提交后 │
│ 全不做 互不干扰 永不丢失 │
│ │ │ │ │ │
│ └──────────┴──────────┴──────────┘ │
│ 三大手段 → 保证 → 目标 │
└──────────────────────────────────────────────────┘
9.3 事务基本操作
-- 显式事务
BEGIN; -- 或 START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;
-- 确认无误后提交
COMMIT;
-- 如果出错,回滚
-- ROLLBACK;
-- 设置保存点(部分回滚)
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 99.00);
SAVEPOINT sp1;
INSERT INTO order_items (order_id, product_name) VALUES (1, '商品A');
-- 如果第二条出错,可以回滚到保存点而不影响第一条
ROLLBACK TO SAVEPOINT sp1;
-- 重新尝试或直接提交第一条
COMMIT;
9.4 锁机制
-- 行级锁:SELECT ... FOR UPDATE(悲观锁)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 其他事务对这行只能等待,防止并发修改
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;
-- FOR UPDATE NOWAIT:获取不到锁立即报错而不等待
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- FOR SHARE:共享锁,允许其他事务读但不允许修改
SELECT * FROM articles WHERE id = 1 FOR SHARE;
-- 乐观锁(应用层实现,推荐)
-- 通过版本号字段控制
UPDATE articles
SET view_count = view_count + 1, version = version + 1
WHERE id = 1 AND version = 5; -- 如果 version 已变,影响行数为 0
悲观锁 vs 乐观锁对比:
| 维度 | 悲观锁 | 乐观锁 |
|---|---|---|
| 策略 | 先锁再操作 | 先操作再验证 |
| 实现 | SELECT ... FOR UPDATE |
版本号/CAS |
| 适用场景 | 写多读少、冲突频繁 | 读多写少、冲突少 |
| 性能 | 并发度低但安全 | 并发度高但可能重试 |
十、索引
10.1 索引类型
PostgreSQL 支持多种索引类型,是其在复杂查询场景下的重要优势:
| 索引类型 | 适用场景 | 示例 |
|---|---|---|
| B-Tree(默认) | 等值查询、范围查询、排序 | CREATE INDEX idx_name ON users(username) |
| Hash | 等值查询 | CREATE INDEX idx_h ON users USING HASH(username) |
| GIN | JSONB、数组、全文检索 | CREATE INDEX idx_gin ON articles USING gin(tags) |
| GiST | 地理信息、范围类型 | CREATE INDEX idx_geo ON shops USING gist(location) |
| BRIN | 大表、时序数据 | CREATE INDEX idx_brin ON logs USING brin(created_at) |
10.2 创建和管理索引
-- 普通索引
CREATE INDEX idx_users_email ON users(email);
-- 唯一索引(功能等同 UNIQUE 约束)
CREATE UNIQUE INDEX idx_users_username ON users(username);
-- 复合索引(注意列顺序:最常用于查询条件的放前面)
CREATE INDEX idx_articles_user_status ON articles(user_id, is_published);
-- 部分索引(只索引符合条件的行,节省空间)
CREATE INDEX idx_articles_published ON articles(published_at)
WHERE is_published = TRUE;
-- 表达式索引(对计算结果建索引)
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- JSONB GIN 索引
CREATE INDEX idx_articles_metadata ON articles USING gin(metadata);
-- 查看表上的索引
\d articles -- psql 命令
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'articles';
-- 删除索引
DROP INDEX idx_users_email;
-- 分析查询是否使用索引(重要!)
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN 输出解读:
Seq Scan:全表扫描(通常需要优化)Index Scan:索引扫描(好)Index Only Scan:仅读索引(最好)Bitmap Index Scan→Bitmap Heap Scan:位图索引扫描(适合匹配多行)
10.3 索引使用原则
- 外键字段建索引:加速 JOIN 操作
- 经常 WHERE / JOIN / ORDER BY 的字段建索引
- 写多读少的表少建索引:索引会影响写入性能
- 复合索引注意列顺序:等值条件放前面,范围条件放后面
- 不要盲目建索引:每个索引都有存储和维护成本
十一、常用内置函数
PostgreSQL 提供了大量内置函数,以下列出开发中最常用的:
11.1 字符串函数
SELECT
LENGTH('PostgreSQL') AS len, -- 10
UPPER('hello') AS upper, -- HELLO
LOWER('WORLD') AS lower, -- world
CONCAT('a', '-', 'b') AS concat, -- a-b
SUBSTRING('PostgreSQL' FROM 1 FOR 3) AS substr, -- Pos
REPLACE('Hello World', 'World', 'PG') AS replace, -- Hello PG
TRIM(' hello ') AS trim_val, -- 'hello'
SPLIT_PART('a,b,c', ',', 2) AS part, -- b
LPAD('42', 5, '0') AS lpad_val, -- 00042
TO_CHAR(1234567, 'FM999,999,999') AS formatted; -- 1,234,567
11.2 日期函数
SELECT
CURRENT_DATE AS today, -- 2025-01-15
CURRENT_TIMESTAMP AS now, -- 2025-01-15 14:30:00+08
NOW() AS now2,
AGE(TIMESTAMP '1990-05-20') AS age, -- 34 years 7 mons
DATE '2025-01-15' + INTEGER '7' AS next_week, -- 2025-01-22
DATE '2025-01-15' - DATE '2025-01-01' AS diff_days, -- 14
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year, -- 2025
TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS formatted; -- 2025-01-15 14:30:00
11.3 聚合函数
SELECT
COUNT(*) AS total,
COUNT(DISTINCT status) AS distinct_status,
SUM(view_count) AS total_views,
AVG(view_count) AS avg_views,
MIN(created_at) AS first_article,
MAX(created_at) AS latest_article
FROM articles;
-- STRING_AGG:将多行字符串拼接为一行
SELECT user_id, STRING_AGG(title, ', ' ORDER BY created_at) AS titles
FROM articles GROUP BY user_id;
-- JSONB_AGG:将多行聚合为 JSON 数组
SELECT user_id, JSONB_AGG(jsonb_build_object('id', id, 'title', title)) AS articles_json
FROM articles GROUP BY user_id;
11.4 条件表达式
-- CASE WHEN
SELECT username,
CASE
WHEN age < 18 THEN '未成年'
WHEN age < 35 THEN '青年'
WHEN age < 60 THEN '中年'
ELSE '老年'
END AS age_group
FROM users;
-- COALESCE:返回第一个非 NULL 值
SELECT COALESCE(nickname, username, '匿名') AS display_name FROM users;
-- NULLIF:两值相等返回 NULL
SELECT NULLIF(status, 0) FROM users;
十二、PostgreSQL 开发最佳实践
12.1 命名规范
| 对象 | 规范 | 示例 |
|---|---|---|
| 表名 | 小写 + 下划线,名词复数或单数 | users、articles |
| 列名 | 小写 + 下划线 | user_id、created_at |
| 索引 | idx_表名_字段 |
idx_users_email |
| 外键 | fk_从表_主表 或 fk_表名_字段 |
fk_articles_user |
| 视图 | v_ 前缀 |
v_published_articles |
12.2 性能优化要点
-- 1. 避免 SELECT *,只查需要的列
-- ❌ SELECT * FROM users;
-- ✅ SELECT id, username, email FROM users;
-- 2. 分页避免大 OFFSET(性能随数据量线性下降)
-- ❌ SELECT * FROM articles ORDER BY id LIMIT 10 OFFSET 100000;
-- ✅ 游标分页
SELECT * FROM articles WHERE id < #{last_id} ORDER BY id DESC LIMIT 10;
-- 3. 批量插入优于循环单条插入
INSERT INTO users (username, email) VALUES
('a', 'a@e.com'), ('b', 'b@e.com'), ('c', 'c@e.com');
-- 4. 使用 EXPLAIN ANALYZE 检查慢查询
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM articles WHERE user_id = 1 ORDER BY created_at DESC;
-- 5. 合理使用索引
-- - 外键字段建索引
-- - 经常 WHERE/JOIN/ORDER BY 的字段建索引
-- - 写多读少的表少建索引(索引影响写入性能)
12.3 psql 常用命令速查
| 命令 | 说明 |
|---|---|
\l |
列出所有数据库 |
\c dbname |
连接到指定数据库 |
\dt |
列出当前数据库的表 |
\d tablename |
查看表结构 |
\du |
列出所有用户/角色 |
\dn |
列出所有 Schema |
\df |
列出所有函数 |
\dx |
列出已安装的扩展 |
\i file.sql |
执行 SQL 文件 |
\timing |
开启 SQL 执行计时 |
\q |
退出 psql |
十三、总结
本文从开发视角系统介绍了 PostgreSQL 的核心知识:
- 安装配置:掌握了 PostgreSQL 18 在 Linux 和 Windows 上的安装与远程访问配置
- 客户端工具:学会了通过 Navicat 连接和管理 PostgreSQL(Linux 远程 + Windows 本地)
- SQL 概述:理解了 SQL 语句分类(DDL/DML/DQL/DCL/TCL)和标识符规则
- 数据库操作:掌握了数据库和 Schema 的创建、切换、删除
- 表与数据类型:熟悉了 PG 丰富的类型系统,特别是 JSONB、数组等特色类型
- 增删改:掌握了 INSERT(含 UPSERT/RETURNING)、UPDATE、DELETE
- 查询:系统学习了 SELECT、条件查询、排序分页、聚合、JOIN、子查询
- 事务:深入理解了 ACID 四大特性和隔离级别的并发问题
- 索引:学会了多种索引类型的使用和 EXPLAIN 执行计划分析
- 内置函数:掌握了字符串、日期、聚合、条件表达式等常用函数
PostgreSQL 不仅仅是一个数据库,它强大的类型系统、扩展能力和 SQL 标准兼容性使其成为复杂业务场景的理想选择。作为 Java 开发者,掌握 PostgreSQL 将为你的技术栈增添一个强有力的工具。
下一步学习建议:
- 深入学习 PostgreSQL 查询优化器与执行计划
- 了解逻辑复制与物理复制(CDC 数据同步)
- 探索 PostGIS 地理信息扩展
- 学习 psycopg2 / JDBC 驱动在 Java 项目中的集成
- 研究 PostgreSQL 的全文检索与中文分词配置
作者:Dailyblue | 博客:天天天蓝
