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 / ANDOR / LIKE / BETWEEN10. ORDER BY / LIMIT / DISTINCT11. GROUP BY / HAVING / 聚合函数12. WITH 公用表表达式（CTE）13. 约束（Constraints）14. JOIN（INNER/LEFT/RIGHT/CROSS）15. UNION16. 子查询17. 索引 + EXPLAIN ANALYZE18. 视图与物化视图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表空间，物理存储位置WALWrite-Ahead Log，预写日志，保证 crash-safePostgreSQL 核心特征函数、索引、触发器、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/RHELdnf install -y postgresql16-servermacOSbrew install postgresql@16Windows官方网站 下载安装包Dockerdocker run --name pg -e POSTGRES_PASSWORD=xxx -d postgres:16pgAdmin（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布尔BOOLEANis_active BOOLEAN日期时间DATE,TIME,TIMESTAMP,TIMESTAMPTZcreated_at TIMESTAMP区间INTERVAL'7 days'::INTERVALJSONJSON,JSONBdata JSONB数组INTEGER[],TEXT[]tags TEXT[]UUIDUUIDuuid 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(age0ANDage100),gradeVARCHAR(2),scoreNUMERIC(4,1),emailVARCHAR(100)UNIQUE,enrolled_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);CREATETABLEcourses(idSERIALPRIMARYKEY,course_nameVARCHAR(100)NOTNULL,teacherVARCHAR(50),creditsINTEGERDEFAULT2CHECK(credits0));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