一、PostgreSQL是什么？

1、PostgreSQL是以4.2版本的POSTGRES为基础的对象关系型数据库管理系统（ORDBMS)，它是由美国加州大学伯利克分校计算机系开发的。

2、它提供了许多现代化功能：

1. 复杂查询
2. 外健
3. 触发器
4. 可更新的视图
5. 事物完整性
6. 多版本并发控制

还可以通过多种方式由用户扩展，例如添加新的

1. 数据类型
2. 函数
3. 操作符
4. 聚合函数
5. 索引方法
6. 过程语言

3、本书内容约定

1. 方括号[]表示可选部分
2. 大括号{}和竖线|表示必须从其中选择一个替代方案
3. 点号(...)表示前面的元素可复用
4. 所有其他符号包括括号应该安字面意思理解
5. shell命令提示符$,sql命令提示符=>

4、架构基础

在数据库术语里，postgresql使用一种客户端/服务器的模型，一次postgresql会话由下列的相关进程组成：

1）服务器进程：

1. 管理数据库文件
2. 接收来自客户端应用与数据库连接
3. 代表客户端在数据库上执行操作

2）需要执行数据库操作的用户的客户端目标守护者服务器进程

5、数据库

1）使用postgresql管理员创建、删除数据库。createdb dbname和dropdb dabname

2）访问数据库

1. 运行psql交互式终端程序，交互式输入、编辑和执行sql命令。
2. 使用图形化的前段工具。
3. 使用多语言绑定中的一种编写自定义程序。

二、SQL语言

1、引言

SQL（Structured Query Language,结构化查询语言)是用于管理关系型数据库的标准语言。

SQL按用途通常分为以下几类：

1）DDL（Data Design Language，数据定义语言）

用于定义和修改数据库结构。

1. create：创建数据库、表、视图、索引等
2. ALTER：修改表结构
3. DROP：删除对象
4. TRUNCATE：清空表数据

2）DML(Data Manipulation Language，数据操作语言）

用于操作表中的数据。

1. SELECT：查询数据
2. INSERT：插入数据
3. UPDATE：更新数据
4. DELETE：删除数据

3）DCL（Data Control Language，数据控制语言）

用于权限和事物控制。

1. GRANT：授予权限
2. REVOKE：收回权限
3. COMMIT/ROLLBACK/SAVEPOINT：事物控制

4）TCL（Thing Control Language，事物控制语言）

有时单独列出，包括COMMIT、ROLLBACK、SAVEPOINT等。

在PostgreSQL中，运行以下命令：

解释：
\i命令从指定的文件中读取命令。
psql的-s选项把你置于单步模式，它在向服务器发送每个语句之前暂停。

2、PostgreSQL概念

1）PostgreSQL

是一种关系型对象数据库管理系统，这意味着它是一种用于管理存储在关系中的数据的系统。

所谓关系，实际上就是表的数学术语，把数据存储在表里的概念已经成为固有的常识了。但是还有其他方法用于组织数据库，譬如Unix系统上的文件和目录就形成了一种层次数据库的例子。更现代的发展是面向对象数据库。

2）表

表是一个命了名的行集合。

一个给定表的没一行由同一组的命名列组成，每一列都有一个特定的数据类型。命名的列称为字段（field）。

虽然列在每行里的顺序是固定的，但sql并不对行在表中的顺序做任何保证。

表被分组到数据库中，由单个Postgresql服务器实例管理的全部数据库的集合，组成了一个数据库集簇。

PostgreSQL 数据类型组织架构

PostgreSQL 数据类型
├── 1. 数值与货币类型族
│   ├── 整数部
│   │   ├── smallint / int2        2字节整数
│   │   ├── integer / int / int4   4字节整数，最常用
│   │   └── bigint / int8          8字节整数
│   ├── 自增语法糖部
│   │   ├── smallserial / serial2
│   │   ├── serial / serial4
│   │   └── bigserial / serial8
│   ├── 精确数值部
│   │   └── numeric / decimal      任意精度，金额/精确小数首选
│   ├── 浮点数值部
│   │   ├── real / float4          4字节，不精确
│   │   └── double precision / float8  8字节，不精确
│   └── 货币部
│       └── money                  货币金额，受 lc_monetary 影响，跨区域慎用
│
├── 2. 布尔类型族
│   └── boolean / bool              true / false / null
│
├── 3. 字符类型族
│   ├── 变长部
│   │   ├── varchar(n) / character varying(n)  有长度限制变长字符串
│   │   └── text                               无长度限制变长字符串，常用
│   ├── 定长部
│   │   └── char(n) / character(n)             定长，空格填充，现代少用
│   └── 内部标识部
│       └── name                               内部标识符类型，系统目录常用
│
├── 4. 二进制与位串类型族
│   ├── 二进制部
│   │   └── bytea                  原始字节数组，如图片、哈希、文件内容
│   └── 位串部
│       ├── bit(n)                 定长位串
│       └── bit varying(n) / varbit(n)  变长位串
│
├── 5. 日期与时间类型族
│   ├── 日期部
│   │   └── date                   日期，年月日
│   ├── 时间部
│   │   ├── time / time without time zone       仅时间
│   │   └── time with time zone / timetz        带时区时间，通常慎用
│   ├── 时间戳部
│   │   ├── timestamp / timestamp without time zone  日期时间，不带时区
│   │   └── timestamptz / timestamp with time zone   日期时间，带时区，记录时间点首选
│   └── 间隔部
│       └── interval               时间间隔，如 1 day 2 hours
│
├── 6. JSON 与 XML 类型族
│   ├── JSON部
│   │   ├── json                   保留原始文本，查询需解析，不支持索引
│   │   └── jsonb                  二进制分解存储，查询快，支持 GIN 索引，默认首选
│   └── XML部
│       └── xml                    存储 XML 数据
│
├── 7. 网络地址类型族
│   ├── inet                       IPv4/IPv6 主机地址
│   ├── cidr                       IPv4/IPv6 网络地址
│   ├── macaddr                    MAC 地址
│   └── macaddr8                   8字节 MAC 地址
│
├── 8. 几何类型族
│   ├── point                      点
│   ├── line                       无限直线
│   ├── lseg                       线段
│   ├── box                        矩形框
│   ├── path                       路径
│   ├── polygon                    多边形
│   └── circle                     圆
│
├── 9. 文本检索类型族
│   ├── tsvector                   文档向量，用于全文检索
│   └── tsquery                    查询表达式，用于全文检索
│
├── 10. UUID 与对象标识类型族
│   ├── UUID部
│   │   └── uuid                   通用唯一标识符，分布式主键常用
│   └── 对象标识部
│       ├── oid                    对象标识符
│       ├── regclass               表/索引等关系 OID 别名
│       ├── regtype                数据类型 OID 别名
│       ├── regproc / regprocedure 函数 OID 别名
│       ├── regoper / regoperator  操作符 OID 别名
│       ├── regrole                角色 OID 别名
│       ├── regnamespace           命名空间 OID 别名
│       └── regconfig / regdictionary 文本检索配置/字典 OID 别名
│
├── 11. 日志序列号类型族
│   └── pg_lsn                      PostgreSQL 日志序列号 LSN
│
├── 12. 用户定义与容器类型族
│   ├── 枚举部
│   │   └── enum                   静态有序枚举值，如订单状态
│   ├── 数组部
│   │   └── 任意类型[]             多维变长数组，如 integer[]、text[][]
│   ├── 复合部
│   │   └── composite type         CREATE TYPE ... AS (...)，行类型/复合结构
│   ├── 范围部
│   │   ├── int4range              整数范围
│   │   ├── int8range              大整数范围
│   │   ├── numrange               numeric 范围
│   │   ├── tsrange                时间戳范围
│   │   ├── tstzrange              带时区时间戳范围
│   │   ├── daterange              日期范围
│   │   └── 自定义范围              CREATE TYPE ... AS RANGE
│   └── 域部
│       └── domain                 CREATE DOMAIN，在基础类型上加约束/默认值
│
├── 13. 伪类型族（函数/过程专用，不能作为普通表列）
│   ├── any
│   ├── anyelement
│   ├── anyarray
│   ├── anynonarray
│   ├── anyenum
│   ├── anyrange
│   ├── cstring
│   ├── internal
│   ├── record
│   ├── trigger
│   ├── void
│   ├── language_handler
│   ├── fdw_handler
│   ├── index_am_handler
│   └── tsm_handler
│
└── 14. 扩展类型族（需安装扩展）
    ├── PostGIS：geometry、geography 等
    ├── hstore：键值对
    ├── citext：大小写不敏感文本
    ├── ltree：标签树/层级路径
    ├── pgvector：vector 向量类型
    └── 其他扩展自定义类型

各部门职责与选型要点#

一句话总览#

PostgreSQL 数据类型可组织为：基础标量部门（数值、字符、布尔、二进制、日期时间、网络、几何、JSON/XML、UUID、文本检索等）＋容器与用户定义部门（数组、复合、范围、枚举、域）＋系统与函数专用部门（对象标识、pg_lsn、伪类型）＋扩展部门（PostGIS、hstore、pgvector 等）。选型核心是：精确性优先、时间点用 timestamptz、JSON 默认 jsonb、容器类型能原生表达就不强行拆表、伪类型不建列。

用在字段（列）上的数据类型，核心原则是必须是在磁盘上有明确存储表示的类型。因此，PostgreSQL 的伪类型（Pseudo-Types），如 anyelement、trigger、internal 等，绝对不可以用作字段类型，它们仅用于定义函数参数或返回值。

排除伪类型后，可用在字段上的数据类型及具体要求如下。

🔢 数值类型#

numeric 的精度与标度：p 是总有效位数，s 是小数点后的位数。NUMERIC(8, 2) 表示总共 8 位数字，其中小数点后 2 位，因此整数部分最多 6 位。如果存入的值标度大于声明值，系统会自动四舍五入到声明的小数位。

📝 字符类型#

🕒 日期与时间类型#

time、timestamp 和 interval 均支持可选的精度 p，用于控制秒的小数部分位数。

• p 的取值范围：0 到 6（默认使用微秒精度，即 p=6）。
• date：无精度参数，仅存储年月日。
• timestamptz：记录“时间点”的首选。它内部以 UTC 存储，显示时按会话时区转换，能正确处理跨时区数据。
• timestamp：不带时区，存储的是“墙上时钟”字面值，不随时区转换。
• interval：表示时间间隔，同样支持 p 来限制秒的小数位精度。

🚀 JSON 类型#

jsonb 字段的约束实践：可以在 jsonb 列上使用 CHECK 约束来强制某些键的存在或值的格式。对于高频查询的 jsonb 路径，需要创建表达式索引（如 CREATE INDEX ... ON tbl ((data->>'key'))）或对整个列创建 GIN 索引，否则查询将全表扫描。

📦 数组类型#

• 定义语法：在任意可用作字段的基础类型后加 []，如 integer[]、text[][]。可以创建内置类型、枚举、复合类型、范围或域的数组。
• 维度与长度：声明时的维度（如 [3][3]）和长度仅为文档说明，PostgreSQL 不会强制执行。但插入多维数组时，每个维度的长度必须严格匹配，否则报错。
• 元素类型一致性：一个数组字段中的所有元素必须属于同一种数据类型。

🎯 用户定义类型（字段层面的特殊要求）#

枚举（ENUM）

• 标签长度限制：每个枚举值（标签）的最大长度受编译参数 NAMEDATALEN 限制，标准安装下为 63 字节。
• 排序内在性：枚举值按声明顺序排序，这会影响 ORDER BY 和范围比较的结果。
• 适用边界：仅适用于封闭、稳定、极少变更的固定值集合（如订单状态）。频繁增删枚举值需要 ALTER TYPE，不适合动态性强的场景。

域（DOMAIN） 域是带约束的基础类型，用于将字段级别的通用约束（如 NOT NULL、CHECK）集中管理。

• 可附加的约束：NOT NULL / NULL、CHECK (expression) 以及 DEFAULT 默认值。
• 约束检查时机：域上的 NOT NULL 和 CHECK 约束在值被转换为域类型时进行检查。
• 字段可用性：域完全可以作为字段类型使用，它是将业务规则抽象到类型层面的有效手段（例如定义一个 email_address 域，自带格式校验 CHECK 约束）。

范围（RANGE） 与 复合（COMPOSITE） int4range、daterange、tstzrange 等范围类型，以及通过 CREATE TYPE ... AS (...) 定义的复合类型，都可以直接用作字段类型，无额外字段级限制。范围类型用于高效表达连续区间，复合类型用于将多个相关属性打包为一个逻辑单元。

💎 快速核对清单#