Posts

事务是数据库中不可分割的工作单元，确保多个SQL操作要么全部成功，要么全部回滚。事务的可靠性由ACID四大特性保障：原子性、一致性、隔离性和持久性。PostgreSQL通过BEGIN、COMMIT和ROLLBACK语句控制事务生命周期，并支持四种隔离级别（读未提交、读已提交、可重复读和串行化）来解决并发冲突。保存点允许事务部分回滚，适用于复杂操作和批量处理。事务管理在电商下单等场景中尤为重要，确保多个操作要么全部成功，要么全部回滚。

PL/pgSQL 是 PostgreSQL 的过程化 SQL 语言，结合了 SQL 的声明式语法和过程式控制结构，用于编写复杂的数据库逻辑。其基本结构包括函数和过程，支持变量声明、参数传递、动态 SQL、条件判断、循环以及错误处理。通过 PL/pgSQL，用户可以实现计算、批量操作、事务控制等功能。函数通过 SELECT 调用，过程通过 CALL 调用，且过程支持显式事务控制。动态 SQL 需使用 EXECUTE 和 USING 避免 SQL 注入。

PostgreSQL视图是基于SQL查询结果的虚拟表，不存储数据，仅保存查询逻辑。视图简化复杂查询、限制数据访问、隔离schema变化。创建视图使用CREATE VIEW语句，支持OR REPLACE替换已有视图、TEMPORARY创建临时视图、RECURSIVE递归视图。视图可指定列名、设置WITH选项（如security_barrier）、CHECK OPTION确保更新行可见。可更新视图需满足特定条件，否则通过INSTEAD OF触发器实现更新。递归视图处理层级结构或序列生成。视图权限默认基于视图所有者，security_invoker以调用者权限访问基础表，security_barrier防止信息泄露。

PostgreSQL索引是提升查询效率的关键工具，类似于书籍目录，帮助快速定位数据。常用索引类型包括B-Tree（默认，适用于等值、范围查询和排序）、Hash（仅等值查询）、GIN（多值类型如数组和JSONB）和BRIN（超大型表）。多列索引需注意列顺序，唯一索引确保数据唯一性，部分索引仅对特定条件数据有效。覆盖索引支持仅索引扫描，避免访问表堆数据。使用EXPLAIN命令检查索引使用情况，确保查询优化。

在数据库设计中，表关系是连接不同表的关键，旨在避免数据冗余和保证数据一致性。PostgreSQL支持三种表关系：一对一、一对多和多对多。一对一关系通过外键同时作为主键实现；一对多关系通过从表的外键指向主表的主键实现；多对多关系通过中间表包含两个外键实现。此外，约束（如主键、外键、唯一、非空和检查约束）确保数据的完整性和一致性。外键的ON DELETE CASCADE和ON DELETE RESTRICT分别用于自动删除关联行或阻止主表行删除。

PostgreSQL中的WHERE子句用于精准筛选数据，支持基本比较与逻辑运算，以及IN、BETWEEN、LIKE、IS NULL等谓词处理复杂场景。ORDER BY子句用于按指定规则排序结果，支持单列、多列及计算列排序。聚合函数如COUNT、SUM、AVG等用于提炼关键信息，GROUP BY与HAVING用于分组分析，WHERE过滤行，HAVING过滤分组结果。综合实践展示了如何组合过滤、排序与聚合，常见报错及解决方法也提供了实用指导。