1.3 PostgreSQL DBMS基本管理

1.3.1  PostgreSQL启动及关闭

PostgreSQL的启动关闭一般使用systemctl、pg_ctl以及直接运行postgres命令来启动、停止、重启、重加载数据库服务，如图1-4 PostgreSQL的启动及关闭的命令。

pg_ctl是PostgreSQL中初始化数据目录，启动、停止、重启、重加载数据库服务，或者查看数据库服务状态的工具，相比SERVICE或SYSTECTL的管理方式，pg_ctl提供了丰富的控制项，执行pg_ctl命令需要操作系统用户使用SU命令切换到POSTGRES用户。 

1.3.2  PostgreSQL数据类型

数据类型是我们在创建表的时候为每个字段设置的,PostgreSQL提供了丰富的数据类型，用户可以使用CREATE TYPE 命令在数据库中创建新的数据类型。

1.数值类型

数值类型由2 字节、4 字节或 8 字节的整数以及 4 字节或 8 字节的浮点数和可选精度的十进制数组成，如表1-2 PostgreSQL数值类型。

2.货币类型

money 类型存储带有固定小数精度的货币金额。

numeric、int 和 bigint 类型的值可以转换为 money，不建议使用浮点数来处理处理货币类型，因为存在舍入错误的可能性。

3.字符类型

PostgreSQL 所支持的字符类型，如下图

4.日期/时间类型

PostgreSQL 所支持的日期/时间类型如表1-4 PostgreSQL日期/时间类型，BC既公元前，数字越大，时间越早；AD既公元后，数字越大，时间越晚。

5.布尔类型

PostgreSQL 支持标准的 boolean 数据类型。

boolean 有"true"(真)或"false"(假)两个状态， 第三种"unknown"(未知)状态，用 NULL 表示。

6.枚举类型

枚举类型是一个包含静态和值的有序集合的数据类型，PostgtesSQL中的枚举类型类似于 C 语言中的 enum 类型，与其他类型不同的是枚举类型需要使用CREATE TYPE 命令创建。

7.XML类型

xml 数据类型可以用于存储XML数据。 将 XML 数据存到 text 类型中的优势在于它能够为结构良好性来检查输入值， 并且还支持函数对其进行类型安全性检查。

8.JSON类型

json 数据类型可以用来存储 JSON（JavaScript Object Notation）数据， 这样的数据也可以存储为 text，但是 json 数据类型更有利于检查每个存储的数值是可用的 JSON 值。

9.数组类型

PostgreSQL 允许将字段定义成变长的多维数组。数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型。

1.3.3  PostgreSQL索引及约束

PostgreSQL 索引的核心作用是 通过预先组织数据，减少查询时需要扫描的数据量，从而极大提升查询性能，比如以下场景：

l 利用索引快速定位到可能满足条件的记录，不需要遍历所有记录。

l 利用索引可直接返回升序和降序的排序结果，无需在每次执行查询时再排序。

l 嵌套循环Join利用索引能快速检索到内层表的关联记录。

PostgreSQL提供了多种索引类型：B-tree、Hash、GiST、SP-GiST、GIN和BRIN,每种都针对特定的查询场景设计。

1.3.4  PostgreSQL视图管理

视图就是被赋予名称并被保存下来的SQL 查询语句。你可以像使用普通数据表一样来使用它，但它本身并不存储数据，是一个“虚拟表”。每次查询视图时，数据库都会实时执行其定义中的查询语句。

使用视图能带来三大核心价值：

简化复杂查询：将涉及多表连接和复杂条件的查询封装成一个简单的视图，后续直接查询视图即可，避免重复编写复杂SQL。

数据安全与抽象：可以屏蔽底层表的敏感字段（如薪资），只暴露必要的、有权限访问的数据。即便底层表结构因业务需求变更（如分库分表），只要视图接口不变，前端应用就无需修改。

逻辑独立性：为复杂的业务规则提供一个统一的查询接口，保证业务逻辑的一致性。

1.3.5  PostgreSQL触发器

PostgreSQL 触发器是数据库中的一种强大机制，它能在特定数据操作（如 INSERT、UPDATE、DELETE）或数据库事件（如 DDL 语句）发生时，自动执行预定义的函数。触发器主要用于实现复杂的数据完整性约束、审计日志、自动维护衍生数据等业务逻辑，将跨应用的功能集中在数据库层面保障一致性，触发器的触发时机主要有三种：

BEFORE：在触发事件（如INSERT）发生之前执行。常用于数据校验或修改插入/更新的数据。例如，在保存前自动生成时间戳或格式化数据。

AFTER：在触发事件完成之后执行。适用于需要访问最终数据的操作，如记录审计日志、同步到汇总表。

INSTEAD OF：主要用于视图，用触发器中的自定义逻辑替代原本对视图的插入、更新或删除操作。

1.3.6  PostgreSQL定时任务

为什么需要数据库定时任务？每天凌晨统计昨日销售额、每小时清理临时数据、每周定时清理日记…难道要人工半夜爬起来执行？定时任务就是数据库的“闹钟”和“自动执行机器人”！

1.3.7  PostgreSQL函数

PostgreSQL提供了四种类型的函数：

SQL语言函数（用SQL编写的函数）。

过程语言函数（用PL/PGSQL、PL/TCL等过程语言编写的）。

内部函数。

C语言函数。