PostgresSQL学习笔记

学习时间：2024年7月27日

学习来源：PostgresSQL 15.7手册

1 概述

1.1 架构基础

PostgreSQL使用一种客户端/服务器的模型。一次PostgreSQL会话由下列相关的进程（程序）组成：

  • 一个服务器进程，它管理数据库文件、接受来自客户端应用与数据库的联接并且代表客户端在数据库上执行操作。 该数据库服务器程序叫做postgres。
  • 那些需要执行数据库操作的用户的客户端（前端）应用。 客户端应用可能本身就是多种多样的：可以是一个面向文本的工具， 也可以是一个图形界面的应用，或者是一个通过访问数据库来显示网页的网页服务器，或者是一个特制的数据库管理工具。 一些客户端应用是和 PostgreSQL发布一起提供的，但绝大部分是用户开发的。

PostgreSQL服务器可以处理来自客户端的多个并发请求。 因此，它为每个连接启动（“forks”）一个新的进程。 从这个时候开始，客户端和新服务器进程就不再经过最初的 postgres进程的干涉进行通讯。 因此，守护者服务器进程总是在运行并等待着客户端联接， 而客户端和相关联的服务器进程则是起起停停。

1.2 和MySQL的比较

1.2.1 设计哲学与标准化支持

  • PostgreSQL：追求对SQL标准的高度遵守，提供了丰富的SQL特性，包括窗口函数、递归查询、全量类型支持等。PostgreSQL注重数据一致性，支持复杂的事务处理和并发控制，适合需要严格遵循ACID原则的企业级应用。

  • MySQL：在早期版本中，MySQL对SQL标准的支持较为有限，但随着时间推移，MySQL也在逐步增加对更多SQL特性的支持。MySQL的设计倾向于易用性和高性能，特别是在读密集型场景下表现优秀，更适合Web应用和中小型企业的需求。

1.2.2 数据类型与特性

  • PostgreSQL：提供了更丰富的数据类型，如数组、JSON、XML、UUID、几何类型等，并支持自定义数据类型。PostgreSQL还支持全文搜索、窗口函数、强大的索引（如BRIN、GiST、GIN、SP-GiST）、表继承、行级安全性等高级特性。
  • MySQL：基础数据类型较为全面，但相比于PostgreSQL，MySQL在高级数据类型和索引支持上略显保守。MySQL 5.7以后增加了JSON支持，但在此之前对JSON的支持不如PostgreSQL成熟。MySQL的InnoDB存储引擎实现了事务处理和行级锁定，但在复杂查询和高级索引方面不及PostgreSQL。

1.2.3 事务处理与并发控制

  • PostgreSQL：支持多版本并发控制（MVCC）和Serializable事务隔离级别，具有更强的并发控制能力，特别适合处理复杂的并发事务场景。
  • MySQL：InnoDB存储引擎同样支持MVCC，但默认的事务隔离级别是Repeatable Read，可根据需要调整。MySQL在大规模并发读写方面的优化较多。

1.2.4 高可用和扩展性

  • PostgreSQL：支持物理复制（流复制）和逻辑复制，可用于构建高可用集群和异地容灾方案。借助第三方工具如pgpool-II、Patroni等，可以实现更复杂的自动故障切换和负载均衡。
  • MySQL：也支持主从复制（异步和半同步）和组复制（MySQL 5.7以后的多节点强一致复制），可通过MySQL Cluster、MySQL Fabric等实现高可用和分布式存储。MySQL也支持Galera Cluster等第三方集群解决方案。

1.2.5 许可与社区支持

  • PostgreSQL：采用非常宽松的BSD许可，完全开源，社区活跃，有很多第三方插件和扩展。
  • MySQL：虽然MySQL Server本身也是开源的，但现在属于Oracle公司，提供GPL许可版本和商业许可版本。MySQL社区仍然活跃，但由于Oracle的商业化运作，一些高级功能可能仅在商业版中提供。

1.2.6 适用场景

  • MySQL 适用于需要处理大量读操作的应用，如 Web 应用程序、电子商务网站和博客平台等。它的简单性和高性能使得它成为许多小型和中型项目的首选。
  • PostgreSQL 适用于需要复杂数据类型和高级特性的应用，如地理信息系统 (GIS)、大数据分析和科学研究等。它的灵活性和丰富的功能使得它成为处理复杂数据和查询的首选。

2 SQL语言

2.1 数据类型

2.1.1 数字类型

名字 存储尺寸 描述 范围
smallint 2字节 小范围整数 -32768 to +32767
integer 4字节 整数的典型选择 -2147483648 to +2147483647
bigint 8字节 大范围整数 -9223372036854775808 to +9223372036854775807
decimal 可变 用户指定精度，精确 最高小数点前131072位，以及小数点后16383位
numeric 可变 用户指定精度，精确 最高小数点前131072位，以及小数点后16383位
real 4字节 可变精度，不精确 6位十进制精度
double precision 8字节 可变精度，不精确 15位十进制精度
smallserial 2字节 自动增加的小整数 1到32767
serial 4字节 自动增加的整数 1到2147483647
bigserial 8字节 自动增长的大整数 1到9223372036854775807

2.1.2 字符类型

名字 描述
character varying(n), varchar(n) 有限制的变长
character(n), char(n) 定长，空格填充
text 无限变长

示例

1
2
3
4
5
6
7
CREATE TABLE test1 (a character(4));
INSERT INTO test1 VALUES ('ok');
SELECT a, char_length(a) FROM test1; -- (1)

  a   | char_length
------+-------------
 ok   |           2

2.1.3 二进制

名字 存储尺寸 描述
bytea 1或4字节外加真正的二进制串 变长二进制串

2.1.4 日期/时间类型

名字 存储尺寸 描述
timestamp [ (p) ] [ without time zone ] 8字节 包括日期和时间（无时区）
timestamp [ (p) ] with time zone 8字节 包括日期和时间，有时区
date 4字节 日期（没有一天中的时间）
time [ (p) ] [ without time zone ] 8字节 一天中的时间（无日期）
time [ (p) ] with time zone 12字节 仅仅是一天中的时间（没有日期），带有时区
interval [ field] [ (p) ] 16字节 时间间隔

2.1.5 布尔类型

名字 存储尺寸 描述
boolean 1字节 状态为真或假

在SQL查询中，布尔常量可以表示为SQL关键字TRUE, FALSE,和 NULL。

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE test1 (a boolean, b text);
INSERT INTO test1 VALUES (TRUE, 'sic est');
INSERT INTO test1 VALUES (FALSE, 'non est');
SELECT * FROM test1;
 a |    b
---+---------
 t | sic est
 f | non est

SELECT * FROM test1 WHERE a;
 a |    b
---+---------
 t | sic est

2.1.6 组合类型

一个组合类型表示一行或一个记录的结构，它本质上就是一个域名和它们数据类型的列表。PostgreSQL允许把组合类型用在很多能用简单类型的地方。例如，一个表的一列可以被声明为一种组合类型。

语法：CREATE TYPE

示例

1
2
3
4
5
6
7
8
9
10
CREATE TYPE complex AS (
  r       double precision,
  i       double precision
);

CREATE TYPE inventory_item AS (
  name            text,
  supplier_id     integer,
  price           numeric
);

2.2 函数和操作符

  • unnest() ：将指定的数组展开为一个行的集合。

语法：

1
2
3
unnest(array) -> set

unnest(array, array[, array ...]) -> set, set [, set ...]

示例

1
2
3
4
5
6
7
SELECT unnest(ARRAY[0, 1, 2]);

 unnest
--------
      0
      1
      2
1
2
3
4
5
6
7
8
9
10
SELECT unnest('[2:4][2:3]={{1,2},{3,4},{5,6}}'::integer[]);

unnest
--------
      1
      2
      3
      4
      5
      6
  • WITH ORDINALITY：用来返回记录的每一行行号。

当 from 语句后面的函数加上 WITH ORDINALITY 属性后，那么返回的结果集将增加一个整数列，这个整数列从1开始，并且按 1 递增。要注意：WITH ORDINALITY必须使用在from子句中，且要紧跟在函数后面使用！

示例

1
2
3
4
5
6
7
8
9
10
SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY; 

 unnest | ordinality 
--------+------------
 a      |          1
 b      |          2
 c      |          3
 d      |          4
 e      |          5
 f      |          6
1
2
3
4
5
6
7
8
9
SELECT * FROM UNNEST(ARRAY[5,4,3,2,1]) WITH ORDINALITY; 

unnest | ordinality 
--------+------------
      5 |          1
      4 |          2
      3 |          3
      2 |          4
      1 |          5
  • WITH

WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE，它们可以被看成是定义只在一个查询中存在的临时表。在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DELETE，并且WITH子句本身也可以被附加到一个主语句，主语句也可以是SELECT、INSERT、UPDATE或DELETE。

3 服务器管理

暂略

4 服务器编程

4.1 扩展SQL

4.2.1 类型系统

4.2.2 用户定义的函数

PostgreSQL提供四种函数：

  • 查询语言函数（用SQL编写的函数）
  • 过程语言函数（例如，用PL/pgSQL或PL/Tcl编写的函数）
  • 内部函数
  • C 语言函数

每一类函数可以采用基本类型、组合类型或者它们的组合作为参数。此外，每一类函数可以返回一个基本类型或一个组合类型。函数也能被定义成返回基本类型或组合类型值的集合。

很多类函数可以接受或者返回特定的伪类型（例如，多态类型），但是可用的功能会变化。

SQL命令：create function

1
2
3
4
5
6
7
8
9
10
CREATE [OR REPLACE] FUNCTION function_name(arguments)
RETURN return_datatype AS $variable_name$
	DECLARE
		declarations;
		[...]
	BEGIN
		<function_body>
		[...]
		RETURN { variable_name | value }
	END; LANGUAGE plpgsql;

  • function_name：指定函数的名称。

  • [OR REPLACE]：是可选的，它允许修改/替换现有函数。

  • DECLARE：定义参数（参数名 类型，例如a integer）。

  • BEGIN~END：在中间写方法主体。

  • RETURN：指定要从函数返回的数据类型（它可以是基础，复合或域类型，或者也可以引用表列的类型）。

  • LANGUAGE：它指定实现该函数的语言的名称。

例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE FUNCTION test(id uuid)
RETURNS INTEGER
LANGUAGE plpgsql;
AS 
$$
	DECLARE
		count INTEGER;
	BEGIN
		count = (SELECT json_array_length(A.json::json -> 'features')
             FROM "Json" A
             WHERE A.uid = id);
		RETURN count;
	END; 
$$

使用：

1
SELECT test(id) -- 返回数组长度

4.2.3 SQL函数

一个 SQL 函数的主体必须是一个由分号分隔的 SQL 语句的列表。最后一个语句之后的分号是可选的。除非函数被声明为返回void，最后一个语句必须是一个SELECT或者一个带有RETURNING子句的INSERT、UPDATE或者DELETE。

① 参数

函数的参数可以在函数体中用名称或编号引用。

② 基本类型上的函数

最简单的SQL函数没有参数并且简单地返回一个基本类型，例如integer：

1
2
3
4
5
6
7
8
9
CREATE FUNCTION one() RETURNS integer AS $$
	SELECT 1 AS result;
$$ LANGUAGE SQL;

SELECT one();

 one -- 注意是one，而不是result
-----
    1

注意我们为该函数的结果在函数体内定义了一个列别名（名为result），但是这个列别名在函数以外是不可见的。因此，结果被标记为one而不是result。

定义用基本类型作为参数的SQL函数：

1
2
3
4
5
6
7
8
9
CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$
	SELECT x + y;
$$ LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

 answer
--------
      3

也能省掉参数的名称而使用数字：

1
2
3
4
5
6
7
8
9
CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
	SELECT $1 + $2;
$$ LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

 answer
--------
      3

实际例子：定义一个函数，该函数从账户上借记一定金额

1
2
3
4
5
6
CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tf1.accountno;
    SELECT 1;
$$ LANGUAGE SQL;

注意：参数accountno和bank表中的列名accountno一致，为区分，需要使用前缀tf1.引用函数的参数。

一个用户可以这样执行这个函数来从账户 17 中借记 $100.00：

1
SELECT tf1(17, 100.0);

实际上我们可能喜欢从该函数得到一个更有用的结果而不是一个常数 1，因此一个更可能的定义是：

1
2
3
4
5
6
CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tf1.accountno;
    SELECT balance FROM bank WHERE accountno = tf1.accountno; -- 返回新的余额
$$ LANGUAGE SQL;

它会调整余额并且返回新的余额。 同样的事情也可以用一个使用RETURNING的命令实现：

1
2
3
4
5
6
CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tf1.accountno
    RETURNING balance; -- 返回新的余额
$$ LANGUAGE SQL;
③ 组合类型上的函数

在编写使用组合类型参数的函数时，我们必须不仅指定我们想要哪些参数，还要指定参数的期望属性（域）。

示例

  • 创建一张表：
1
2
3
4
5
6
7
8
CREATE TABLE emp (
    name        text,
    salary      numeric,
    age         integer,
    cubicle     point
);

INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)');
  • 编写一个函数，作用是计算某个人的双倍薪水。
1
2
3
CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
    SELECT $1.salary * 2 AS salary;
$$ LANGUAGE SQL;

这里的$1就是参数里的emp表（组合类型），然后引用字段salary。

  • 使用
1
2
3
4
5
6
7
SELECT name, double_salary(emp.*) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

 name | dream
------+-------
 Bill |  8400

注意调用的SELECT命令是如何使用table_name.*来选择一个表的整个当前行作为一个组合值的。该表行也可以只用表名来引用：

1
2
3
SELECT name, double_salary(emp) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';
④ 带有输出参数的函数