PostgreSQL 16 实战入门：从安装到高级特性，Ubuntu 手把手教程
环境：华为云 FlexusX x2e.8u.16g（8vCPU/16GiB）| Ubuntu 24.04 | PostgreSQL 16.14
作者：小森 🌲
适用人群：有 SQL 基础，想系统学习 PostgreSQL 的开发者
目录
- 1. PostgreSQL 简介
- 2. 安装 PostgreSQL（Ubuntu 24.04）
- 3. PostgreSQL 基本语法
- 4. 数据类型速览
- 5. 数据库操作（创建/选择/删除）
- 6. 表操作（创建/删除/ALTER/TRUNCATE）
- 7. Schema 模式
- 8. CRUD 基础（INSERT/SELECT/UPDATE/DELETE）
- 9. WHERE / AND&OR / LIKE / BETWEEN
- 10. ORDER BY / LIMIT / DISTINCT
- 11. GROUP BY / HAVING / 聚合函数
- 12. WITH 公用表表达式（CTE）
- 13. 约束（Constraints）
- 14. JOIN（INNER/LEFT/RIGHT/CROSS）
- 15. UNION
- 16. 子查询
- 17. 索引 + EXPLAIN ANALYZE
- 18. 视图与物化视图
- 19. 触发器（Trigger）
- 20. 事务与锁
- 21. 权限管理
- 22. JSON 支持（PostgreSQL 特色）
- 23. 日期时间函数
- 24. 常用函数速查
- 25. 窗口函数（Window Functions）
- 26. AUTO INCREMENT（SERIAL vs IDENTITY）
- 27. NULL 处理 / 别名
- 28. Python 接口（psycopg2）
- 29. 踩坑记录
1. PostgreSQL 简介
PostgreSQL 是一个开源的对象-关系数据库管理系统（ORDBMS），BSD 协议发行。
PostgreSQL vs MySQL vs SQLite 对比
┌──────────────────┬─────────────────────┬──────────────────┬──────────────────┐ │ 特性 │ PostgreSQL 16 │ MySQL 8.0 │ SQLite 3 │ ├──────────────────┼─────────────────────┼──────────────────┼──────────────────┤ │ 类型 │ ORDBMS │ RDBMS │ 嵌入式 RDBMS │ │ 许可证 │ BSD │ GPLv2 │ Public Domain │ │ ACID │ 完整支持 │ InnoDB 支持 │ 支持 │ │ 并发控制 │ MVCC │ MVCC │ 文件锁 │ │ JSON 支持 │ JSON/JSONB (原生) │ JSON (5.7+) │ JSON1 (3.9+) │ │ 全文检索 │ 内置 │ 内置 │ FTS3/FTS5 │ │ 窗口函数 │ 完整 │ 8.0+ │ 3.25+ │ │ CTE / 递归CTE │ 支持 │ 8.0+ │ 3.8.3+ │ │ 物化视图 │ 原生支持 │ 不支持 │ 不支持 │ │ 数组/范围类型 │ 原生支持 │ 不支持 │ 不支持 │ │ 触发器语言 │ PL/pgSQL 等多种 │ SQL/PSM │ 不支持存储过程 │ │ 适用场景 │ 复杂查询/分析/企业 │ Web 应用/高并发 │ 移动端/桌面/嵌入 │ └──────────────────┴─────────────────────┴──────────────────┴──────────────────┘关键术语
| 术语 | 说明 |
|---|---|
| ORDBMS | 对象关系数据库系统，关系模型 + 面向对象特性 |
| MVCC | 多版本并发控制，每个事务看到数据快照，无读写阻塞 |
| Schema | 模式/命名空间，逻辑上的一组表集合 |
| Tablespace | 表空间，物理存储位置 |
| WAL | Write-Ahead Log，预写日志，保证 crash-safe |
PostgreSQL 核心特征
函数、索引、触发器、MVCC、规则（RULE）、丰富数据类型、全文检索、NoSQL（JSONB/HStore）、数据仓库。
2. 安装 PostgreSQL（Ubuntu 24.04）
Step 1：安装
apt-getupdateapt-getinstall-ypostgresql postgresql-client安装后自动启动，默认端口5432：
$ pg_lsclusters Ver Cluster Port Status Owner Data directory Logfile16main5432online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log $ psql--versionpsql(PostgreSQL)16.14(Ubuntu16.14-0ubuntu0.24.04.1)Step 2：切换用户
PostgreSQL 安装时自动创建系统用户postgres，需要切换到该用户操作数据库：
su- postgres psql其他平台安装
| 平台 | 方法 |
|---|---|
| CentOS/RHEL | dnf install -y postgresql16-server |
| macOS | brew install postgresql@16 |
| Windows | 官方网站 下载安装包 |
| Docker | docker run --name pg -e POSTGRES_PASSWORD=xxx -d postgres:16 |
pgAdmin（Web 管理界面）
# Ubuntu 安装apt-getinstall-ypgadmin4-web# 配置 Web 访问/usr/pgadmin4/bin/setup-web.sh3. PostgreSQL 基本语法
SQL 语句结构
-- 单行注释/* 多行注释 */-- PostgreSQL 特有的类型转换语法SELECT123::TEXT;-- :: 是类型转换操作符SELECTCAST(123ASTEXT);-- 标准 SQL 写法psql 元命令速查
| 命令 | 说明 |
|---|---|
\l | 列出所有数据库 |
\c dbname | 切换数据库 |
\dt | 列出当前库所有表 |
\d tablename | 查看表结构 |
\di | 列出索引 |
\dn | 列出 Schema |
\du | 列出用户/角色 |
\dp tablename | 查看权限 |
\x | 切换扩展显示模式 |
\q | 退出 |
4. 数据类型速览
| 分类 | 类型 | 示例 |
|---|---|---|
| 整数 | SMALLINT(2B),INTEGER(4B),BIGINT(8B) | age INTEGER |
| 自增 | SERIAL(4B),BIGSERIAL(8B) | id SERIAL PRIMARY KEY |
| 浮点 | REAL(4B),DOUBLE PRECISION(8B) | — |
| 精确小数 | NUMERIC(p,s),DECIMAL(p,s) | score NUMERIC(4,1) |
| 变长字符串 | VARCHAR(n),CHARACTER VARYING(n) | name VARCHAR(50) |
| 定长字符串 | CHAR(n) | grade VARCHAR(2) |
| 文本 | TEXT(无限长) | bio TEXT |
| 布尔 | BOOLEAN | is_active BOOLEAN |
| 日期时间 | DATE,TIME,TIMESTAMP,TIMESTAMPTZ | created_at TIMESTAMP |
| 区间 | INTERVAL | '7 days'::INTERVAL |
| JSON | JSON,JSONB | data JSONB |
| 数组 | INTEGER[],TEXT[] | tags TEXT[] |
| UUID | UUID | uuid UUID |
| 枚举 | CREATE TYPE ... AS ENUM | — |
| 范围 | INT4RANGE,DATERANGE等 | — |
5. 数据库操作（创建/选择/删除）
-- 创建数据库CREATEDATABASEschooldb;-- 查看所有数据库\l-- 选择/切换数据库\c schooldb;-- 删除数据库（需断开连接）DROPDATABASEschooldb;实操输出：
$ \l List of databases Name | Owner | Encoding | Locale Provider | Collate | Ctype | Access privileges -----------+----------+----------+-----------------+-------------+-------------+----------------------- postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | schooldb | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | | postgres=CTc/postgres (4 rows)template0 / template1：创建新数据库时的模板，不可删除。
6. 表操作（创建/删除/ALTER/TRUNCATE）
创建表
CREATETABLEstudents(idSERIALPRIMARYKEY,nameVARCHAR(50)NOTNULL,ageINTEGERCHECK(age>0ANDage<100),gradeVARCHAR(2),scoreNUMERIC(4,1),emailVARCHAR(100)UNIQUE,enrolled_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);CREATETABLEcourses(idSERIALPRIMARYKEY,course_nameVARCHAR(100)NOTNULL,teacherVARCHAR(50),creditsINTEGERDEFAULT2CHECK(credits>0));CREATETABLEenrollments(student_idINTEGERREFERENCESstudents(id)ONDELETECASCADE,course_idINTEGERREFERENCEScourses(id)ONDELETECASCADE,enroll_dateDATEDEFAULTCURRENT_DATE,PRIMARYKEY(student_id,course_id));查看表结构
$ \d students Table "public.students" Column | Type | Collation | Nullable | Default -------------+-----------------------------+-----------+----------+-------------------------------------- id | integer | | not null | nextval('students_id_seq'::regclass) name | character varying(50) | | not null | age | integer | | | grade | character varying(2) | | | score | numeric(4,1) | | | email | character varying(100) | | | enrolled_at | timestamp without time zone | | | CURRENT_TIMESTAMP Indexes: "students_pkey" PRIMARY KEY, btree (id) "students_email_key" UNIQUE CONSTRAINT, btree (email) Check constraints: "students_age_check" CHECK (age > 0 AND age < 100) Referenced by: TABLE "enrollments" CONSTRAINT "enrollments_student_id_fkey" FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADEALTER TABLE
-- 添加列ALTERTABLEstudentsADDCOLUMNphoneVARCHAR(20);ALTERTABLEstudentsADDCOLUMNstatusVARCHAR(10)DEFAULT'active';-- 修改默认值ALTERTABLEstudentsALTERCOLUMNstatusSETDEFAULT'active';-- 添加约束ALTERTABLEstudentsADDCONSTRAINTchk_statusCHECK(statusIN('active','inactive','graduated'));TRUNCATE TABLE
-- 快速清空表（不触发逐行 DELETE）CREATETEMPTABLEtmp_testASSELECT*FROMstudentsLIMIT3;SELECTCOUNT(*)FROMtmp_test;-- 3 行TRUNCATETABLEtmp_test;SELECTCOUNT(*)FROMtmp_test;-- 0 行DROPTABLEtmp_test;DELETE vs TRUNCATE：TRUNCATE 是 DDL 操作，不触发触发器，不可回滚（在事务外），速度极快。
7. Schema 模式
Schema 是表、视图、函数等对象的命名空间，相当于 MySQL 的 Database 概念。
CREATESCHEMAIFNOTEXISTSschool;CREATETABLEschool.teachers(idSERIALPRIMARYKEY,nameVARCHAR(50),subjectVARCHAR(50));INSERTINTOschool.teachers(name,subject)VALUES('王教授','数学'),('李教授','物理');实操输出：
$ \dn List of schemas Name | Owner
