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 下载安装包

  1. 访问 PostgreSQL 官方下载页面:https://www.postgresql.org/download/windows/
  2. 下载 EnterpriseDB 提供的 PostgreSQL 18 安装包(.exe)

如果官方页面尚未提供 PG 18 的 Windows 安装包,也可使用 PostgreSQL 的交互式安装程序 EDB,地址:https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

2.2.2 运行安装向导

  1. 双击运行下载的 .exe 安装程序
  2. 安装目录:默认 C:\Program Files\PostgreSQL\18,可自定义
  3. 组件选择:勾选全部组件
    • PostgreSQL Server(数据库服务端)
    • pgAdmin 4(官方图形管理工具)
    • Stack Builder(扩展安装工具)
    • Command Line Tools(命令行工具)
  4. 数据目录:默认 C:\Program Files\PostgreSQL\18\data
  5. 超级用户密码:为 postgres 用户设置密码(请牢记,后续连接必须使用)
  6. 端口:默认 5432,一般保持默认
  7. 区域设置:选择 Default locale 即可
  8. 点击 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 的核心知识:

  1. 安装配置:掌握了 PostgreSQL 18 在 Linux 和 Windows 上的安装与远程访问配置
  2. 客户端工具:学会了通过 Navicat 连接和管理 PostgreSQL(Linux 远程 + Windows 本地)
  3. SQL 概述:理解了 SQL 语句分类(DDL/DML/DQL/DCL/TCL)和标识符规则
  4. 数据库操作:掌握了数据库和 Schema 的创建、切换、删除
  5. 表与数据类型:熟悉了 PG 丰富的类型系统,特别是 JSONB、数组等特色类型
  6. 增删改:掌握了 INSERT(含 UPSERT/RETURNING)、UPDATE、DELETE
  7. 查询:系统学习了 SELECT、条件查询、排序分页、聚合、JOIN、子查询
  8. 事务:深入理解了 ACID 四大特性和隔离级别的并发问题
  9. 索引:学会了多种索引类型的使用和 EXPLAIN 执行计划分析
  10. 内置函数:掌握了字符串、日期、聚合、条件表达式等常用函数

PostgreSQL 不仅仅是一个数据库,它强大的类型系统、扩展能力和 SQL 标准兼容性使其成为复杂业务场景的理想选择。作为 Java 开发者,掌握 PostgreSQL 将为你的技术栈增添一个强有力的工具。

下一步学习建议:

  • 深入学习 PostgreSQL 查询优化器与执行计划
  • 了解逻辑复制与物理复制(CDC 数据同步)
  • 探索 PostGIS 地理信息扩展
  • 学习 psycopg2 / JDBC 驱动在 Java 项目中的集成
  • 研究 PostgreSQL 的全文检索与中文分词配置

作者:Dailyblue | 博客:天天天蓝

By admin

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注