《PostgreSQL即学即用》学习笔记

imjoey commented 7 years ago

前言

在我的工作中， PostgreSQL 已经逐步替代了MySQL。选择 PostgreSQL 的原因有以下几点：

MySQL 被 Oracle 收了
PostgreSQL 支持如 hstore、range、GIS、json 等更多的数据类型
PostgreSQL 在大数据领域有 Greenplum
PostgreSQL 有便捷的 extension 机制

虽然知道 PostgreSQL 的优点，但对其了解还很不够，本文是《PostgreSQL 即学即用》一书的读书笔记，方便以后自己查询。

本文之后的 PostgreSQL 会简写为 postgres。

imjoey commented 7 years ago

数据库管理

模板数据库（P28）

postgres 中有模板数据库的概念，作为创建数据库时的模板。执行 CREATE DATABASE mydb 创建数据库时，实际上是基于名为 template1 的模板数据库创建的。

基于某个模板数据库创建数据库的完整命令是： CREATE DATABASE my_db TEMPLATE my_template_db;

基于 schema 做数据隔离（P29）

schema 可以对 database 中的 table 进行逻辑分组管理。创建 database 后，会自动创建一个名为 public 的 schema，创建 table 时，默认都会放入到 public.schema 中。在不同的 schema 中，table 名称是可以重复的。

可以为每个 schema 创建一个与之同名的可登录角色，这样当不同角色登录到数据库并进行数据编辑时，只能访问各自的 schema 中的数据。

为了能够保证不同角色使用的 SQL 语句都相同（即不用写 SELECT * FROM AAA.dogs; SELECT * FROM BBB.dogs;，可以通过设置 search_path 变量来解决，比如设定 search_path 为 “public, AAA, BBB”，规划器会按照从 public.schema 到 AAA.schema 到 BBB.schema 的顺序来寻找 dogs 表。

PostgreSQL 中有一个系统变量叫 user，表示当前登录用户的名称，执行 SELECT user 就可以看到其名称。利用这一点，设置 postgresql.conf 中的 search_path 变量为：search_path = “$user, public;”。这样设置后，如果当前登录角色是 AAA，那么所有查询都优先去 AAA.schema 中寻找目标表，找不到才会去 public.schema 下找。

imjoey commented 7 years ago

数据类型

serial 类型（sequence）（P81）

serial 类型和 bigserial 类型是两种可以自动生成递增整数值的数据类型。

建表时如果指定了一个字段的类型是 serial，那么 postgres 会首先将其作为整型处理，同时自动在该表所在 schema 中创建一个名为 table_name_column_name_sql 的序列（sequence），并设定该序列是该整型字段的取值来源。如果删除表中的此 serial 字段，系统同时也会自动删除对应的序列。

在 postgres 中，序列本身也是一种数据库资产，可以使用CREATE SEQUENCE 来创建序列，并可设置序列的当前值、边界值和递增步长。

可以实现多表共用同一个序列，先将字段定义为 integer 或 bigint 类型，然后指定其默认值为 nextval(sequence_name) 即可。

sequence 相关函数：

nextval(' sequence_name ') - 返回 sequence 的下一个自增值

currval(' sequence_name ') - 返回最后一个 nextval 命令返回的值，如果没有调用过 nextval，则不返回任何值

setval(' sequence_name ', n) - 设定 sequence 的当前值

时间类型（P85）

postgres 对时间类型的支持在业界无人能及，更支持时区，还能按照不同的时区对夏令时进行自动转换。而且还支持 interval 类型，可以用于对日期时间进行数学运算。

在当前 postgres 9.4 中，共支持 9 种时间相关的类型。

date 存储月/日/年，没有时区/时/分/秒信息。
time （time without time zone）存储时/分/秒信息，不含日期和时区。
timetz （time with time zone） time 类型加上时区信息。
timestamp（timestamp without time zone）存储了月/日/年/时/分/秒信息，不含时区。即使修改了时区，该类字段查询出来显示的值也是固定的。
timestamptz（timestamp with time zone） timestamp 类型加上时区。该类型字段存储到数据库中的值是以 UTC 世界标准时间格式存储的。当查询时，会按照数据库所在服务器的时区进行换算后再显示。
interval 描述一个时间段的长度，单位可以是小时、天、月、分钟或其他粒度。该类型适用于对日期和事件进行数学运算的场景。其支持：'1 12:59:10' 和 '1 day 12 hours 59 min 10 sec'，表示相同的含义
tsrange 在 postgres 9.2 版本引入，用于定义 timestamp without time zone 的开区间和闭区间，该类型包含两个时间戳以及开区间和闭区间限定符，例如：'[2012-01-01 14:00 2012-01-01 15:00)::tsrange'定义了从14点开始到15点之前结束的一个时间段。
tstzrange 在 postgres 9.2 版本引入，用于定义 timestamp with time zone 的开区间和闭区间。
daterange 在 postgres 9.2 版本引入，用于定义日期的开区间和闭区间。

时区在 postgres 中存取的内部机制：

在存储带时区的时间（如：2012-02-14 18:08:00-8）时，postgres 内部是这样运作的：

通过计算得到其代表的 UTC 标准时间，就是 2012-02-15 04:08:00-0；

将上步计算得到的 UTC 标准时间存储下来；在回调该数据用于显示时，postgres 内部是这样运作的：

找到数据库服务器所观察到的时区或者请求的时区，如：America/New_York；

计算该时区相对于 UTC 标准时间的时差，对于 America/New_York 时区来说，与 UTC 的时差是-5小时；

根据 UTC 标准时间和时区的时差计算出当地时间；

显示计算结果；

有关时区的运算符和函数

计算带时区的时间差 SELECT '2012-03-11 3:10 AM America/Los_Angeles'::timestamptz - '2012-03-11 1:50 AM America/Los_Angeles'::timestamptz ;

将带时区的 timestamptz 转换为不带时区的 timestamp SELECT '2012-02-28 10:00 PM America/Los_Angeles'::timestamptz AT TIME ZONE 'Europe/Paris';

时间类型加上一段时间间隔 SELECT '2012-02-20 11:10 PM'::timestamp + '1 hour'::interval;' 输出：2012-02-11 00:00

使用 OVERLAPS 函数判断两个时间段是否有重叠，如果有返回 True（t），没有返回 False（f） SELECT ('2012-02-20 11:10 PM'::timestamp, '2012-02-25 11:10 PM'::timestamp) OVERLAPS ('2012-02-10 11:10 PM'::timestamp, '2012-02-22 11:10 PM'::timestamp) AS x;，输出： x

t

使用 OVERLAPS 函数判断两个时间段是否有重叠，如果有返回 True（t），没有返回 False（f） `SELECT ('2012-02-20 11:10 PM'::timestamp, '2012-02-25 11:10 PM'::timestamp) OVERLAPS ('2012-02-10 11:10 PM'::timestamp, '2012-02-22 11:10 PM'::timestamp) AS x;`，输出： x
t

区间类型（P94）

在 postgres 9.2 版本中引入，该数据类型可以定义一个值区间。postgres为区间类型提供了丰富的配套运算符和函数，如判定区间是否重叠、判定某个值是否落在区间内、以及相邻若干区间合并为一个完整区间等。

支持的区间类型

postgres 原生支持六种区间类型，都是关于数字型和日期时间型。

int4range int8range 整数型离线区间。
numrange 连续区间，可以用于描述小数、浮点数或双精度数字区间。
daterange 不带时区信息的日期离线区间。
tsrange tstzrange 时间戳（日期加时间）类型的连续区间，秒值部分支持小数，tsrange 不带时区，tstzrange 带时区信息。

定义区间的方法

区间定义时推荐使用前闭后开的模式，即：[)，如果不是规范写法，则存储时自动转为“[)”模式。

SELECT '[2013-01-04, 2013-08-13)'::daterange;   // 这种写法符合规范
SELECT '(2013-01-04, 2013-08-13)'::daterange;   // 这种写法不规范
SELECT '(0,)'::intrange;    // 大于0 小于等于无穷大的整数区间，写法不规范
SELECT '(2013-01-04 10:00, 2013-08-13 10:00]'::tsrange;   // 不规范

此外，也可以使用 constructor range 函数来定义，函数名称与区间名称相同，可以输入两个或三个参数，实例如下：

SELECT daterange('2013-01-05', 'infinity', '[]');    // 定义了一个从2013-01-05到无穷大的前闭后闭区间

imjoey / blog