PostgreSQL

一、简介

PostgreSQL 是一个功能强大的开源对象关系数据库系统，它使用并扩展了 SQL 语言，并结合了许多可安全存储和扩展最复杂数据工作负载的特性。PostgreSQL 的起源可以追溯到 1986 年，作为 POSTGRES 项目的一部分，并在核心平台上进行了 30 多年的积极开发。

PostgreSQL 因其久经考验的架构、可靠性、数据完整性、强大的功能集、可扩展性以及软件背后的开源社区致力于始终如一地提供高性能和创新解决方案而赢得了良好的声誉。PostgreSQL 可在所有主要操作系统一直符合 ACID，并具有强大的附加组件，例如流行的PostGIS地理空间数据库扩展器。毫不奇怪，PostgreSQL 已成为许多人和组织选择的开源关系数据库。

PostgreSQL 具有许多功能，旨在帮助开发人员构建应用程序、帮助管理员保护数据完整性和构建容错环境，并帮助您管理数据，无论数据集大小。除了免费和开源，PostgreSQL 还具有高度可扩展性。例如，您可以定义自己的数据类型、构建自定义函数，甚至使用不同的编程语言而无需重新编译数据库！

PostgreSQL 试图符合SQL 标准，这样的一致性不会与传统特性相矛盾或可能导致糟糕的架构决策。支持 SQL 标准所需的许多功能，但有时语法或功能略有不同。随着时间的推移，可以预期进一步朝着一致性迈进。从 2021 年 9 月发布的第 14 版开始，PostgreSQL 至少符合 SQL:2016 Core 一致性的 179 个强制性特性中的 170 个。在撰写本文时，没有任何关系数据库完全符合此标准。

以上内容摘自官网：https://www.postgresql.org/about/

二、安装和操作

Win 下的安装请参见浪子的首页 Windows 环境配置一章的内容。PG 操作推荐网站：https://www.sjkjc.com/postgresql

一些 PostgreSQL Shell 命令

# 连接本地数据库
psql -U [username] -W dbname
# 连接到远程数据库
psql -h [host] -U [username] -W dbname

# 修改密码
ALTER USER postgres WITH PASSWORD 'postgres';
# 显示所有数据库
\l
# 显示当前数据库的所有表
\d
# 显示数据库的表空间
\db+
# 进入指定的数据库
\c dbname
# 退出bash
\q

自增列

PG 中在创建表的时候不像 MySQL 那样可以直接设置主键自增，它有两种方式设置(本质都是序列的方式实现自增，速度快性能好)：

第一种，自增列使用 bigserial 类型；

1
2
3

CREATE TABLE table_name (
    id bigserial primary key
);

第二种，先创建序列，然后使用序列：

-- 创建一个序列 sequence_name 自己命名的序列名称
CREATE SEQUENCE sequence_name;
-- 若表还未创建
CREATE TABLE table_name (
    id int8 NOT NULL DEFAULT nextval('sequence_name')
);
-- 如果是表已经存在：修改表字段的默认值为序列名称
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT nextval('sequence_name'::regclass);

第一种的本质和第二种是一样的，只是省去了我们自己创建序列的步骤，但是使用第二种可以自定义序列名称。

PG 中的自增列类型并不是很好用，回滚时自增列的 ID 同样会被占用，造成 ID 的不连续（优点上面已经说了）。如果使用，请使用 bigserial 类型。

如上，bigserial 类型差不多能够普通程序使用到声明周期结束，不需要考虑太多。如果确定能够到地球毁灭，可以使用其它方式定义主键，追求有规律递增，例如雪花算法；

字符串

在 PG 中，推荐字符串都使用 text 类型（无长度限制，但会根据内容长度大小占用空间，即不会造成空间浪费）；如果需要限定内容长度时可以使用 varchar(n) 类型。text、varchar 根据内容占用空间，char 占用固定大小空间，如果不够会在末尾添加空格填充。

外键规则

PG 中有五种外键规则：

无动作(No Action)
- ON DELETE: 如果试图删除主表中的行，而该行有从表中的外键引用，操作将被拒绝，不会删除主表的行。
- ON UPDATE: 如果主表中的行被更新，但更新后的新值不再允许有外键引用，操作将被拒绝。
级联(CASCADE)
- ON DELETE: 如果主表中的行被删除，所有依赖于该行的从表行也将被删除。
- ON UPDATE: 如果主表中的行被更新，且新的值不再允许从表中的某个行引用，那么那些行也会被删除或更新以保持一致性。
限制(RESTRICT)
- ON DELETE: 类似于 NO ACTION，但会抛出一个错误，而不是简单拒绝操作。
- ON UPDATE: 同样，如果更新导致违反引用完整性，也会抛出错误。
设为空(SET NULL)
- ON DELETE: 主表中的行被删除时，从表中对应的外键字段将被设置为NULL。
- ON UPDATE: 主表中的行被更新时，如果新的值不再允许从表中的某个行引用，那么该字段将被设置为 NULL。
设为默认(SET DEFAULT)
- ON DELETE: 主表中的行被删除时，从表中对应的外键字段将被设置为默认值（如果定义了默认值）。
- ON UPDATE: 同样，如果新的值不再允许引用，字段将被设置为默认值。

SET NULL 和 SET DEFAULT 要确保该字段可以为 null 或者设有默认值，否则无法使用。

SQL 约束 Deferrable、Deferred

PG 中有三种约束，在创建时可以指定：DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY IMMEDIATE, NOT DEFERRABLE。某些情况下，推迟执行 SQL 很有必要。

目前，只有 UNIQUE, PRIMARY KEY, REFERENCES (foreign key), 和 EXCLUDE 的约束受此设置(SET CONSTRAINTS)的影响。

Deferrable：是否可以推迟到当前事务结束。这意味着，当您尝试插入或更新数据时，不会立即检查约束，而是在事务结束时检查约束。在需要暂时违反复杂事务的约束的情况下，或者希望确保单个事务中多个相关操作的数据一致性时，该约束非常有用。

Deferred：它常出现在 Deferrable 约束的事务上下文中，即使用 Deferred 的前提是已经使用了 Deferrable 约束事务。它在事务中不会检查约束，只有在提交事务时才会验证数据的终态。也可用于在事务持续时间内暂时暂停约束检查。

-- 声明延迟检测约束
CREATE TABLE my_table (
  id SERIAL PRIMARY KEY,
  value NUMERIC CHECK (value > 0) DEFERRABLE
);

-- 事务开始
BEGIN;
-- 设置延迟检测约束
SET DEFERRED;
-- 插入值 -1，违反了约束条件
INSERT INTO my_table (value) VALUES (-1);

-- 这里，没有执行约束检查

-- 提交事务
COMMIT;

-- 根据最终状态确定是否违反约束规则

https://begriffs.com/posts/2017-08-27-deferrable-sql-constraints.html

附

PG SQL

-- 生成指定的一段时间的连续日期，学了这个我感觉 MySQL5.7 是个什么垃圾！！！
select to_char(cdate,'YYYY-MM-DD') as date from
generate_series(to_timestamp('2023-03-27','YYYY-MM-DD'), to_timestamp('2023-04-01','YYYY-MM-DD'),'1 days') as cdate
order by date

PG 的 generate_series 函数可以学习一下，非常 nice：https://www.cnblogs.com/mchina/archive/2013/04/03/2997722.html