本文假设您已掌握了 MySQL 的基本使用,现在快速学习 PostgreSQL。主要介绍两者差异化语法。

为什么学习 PostgreSQL

最初我了解 PostgreSQL 是数据库课上,我们老师非常的特别,因为多数老师数据库教学都会选择 MySQL。

作为全英文授课,这位老师的英文教学格外清晰易懂,我英语不好但听她上课非常舒服,她讲到:由于 MySQL 被 Oracle 收购,MySQL 虽然之前一直因为开源而备受青睐,近年却颇有商业化趋势。PostgreSQL 被越来越多的当作 MySQL 的替代品被应用。

另外,PostgreSQL 由于诞生晚于 MySQL 不仅性能上更加优越,支持上更广泛,语法也更现代化。不过在学习 PostgreSQL 的过程中也会发现其一些值得诟病的地方,这里不展开描述。

个人在学习数据库课程中的感受是,PostgreSQL 在社区支持方面不如 MySQL,因此感觉在中文社区学习 MySQL 然后转向 PostgreSQL 是一个不错的选择。

下载与安装

这里我们直接选择使用图形化工具,你可以选择 PgAdmin ,它是官方开源的。

PostgreSQL 官方下载:PostgreSQL: Downloads

PgAdmin 官方下载:Download (pgadmin.org)

PostgreSQL 官方文档:PostgreSQL 16.1 Documentation

运算与函数

字符串

PostgreSQL 与 MySQL 不同,其字符串必须以单引号 '' (不能使用双引号)括起来,另外其字符串区分大小写,而 MySQL 字符串不区分大小写。

PostgreSQL 使用运算符支持了正则表达式匹配,这也是 PostgreSQL 先进的地方。正则表达式运算符有 ~~*,前者是区分大小写的,后者不区分大小写,用法和 LIKE 一样。虽然使用这个运算符可能有些奇怪,但是还是很方便的。

强制类型转换

MySQL 作强制类型转换使用 CAST / CONVERT 函数,而且使用的不是一套类型名。而 PostgreSQL 使用 :: 运算符进行类型转换。

1
2
SELECT {计算表达式}::{类型} ...
-- SELECT netWorth::decimal FROM MovieExec;

获取时间字段

MySQL 获取时间字段使用大量函数,而 PostgreSQL 使用一个 EXTRACT 函数,它可以从时间字段中截取你需要的部分。

1
2
SELECT EXTRACT({字段} FROM {计算表达式}) ...
-- SELECT EXTRACT(EPOCH FROM birthDate) FROM MovieStar;

联结

JOIN 中与 MySQL 的细微区别

在 PostgreSQL 中,JOIN 必须拥有 ON 关键字,在 MySQL 中可以没有,即直接表示笛卡尔积。

但是在 PostgreSQL 中,你可以使用 FULL JOIN 表示笛卡尔积,不需要 ON 关键字。

1
2
3
SELECT * FROM A JOIN B;  -- 语法错误
SELECT * FROM A JOIN B ON TRUE; -- 正确
SELECT * FROM A FULL JOIN B; -- 正确

约束

CHECK 自定义约束

我们在 MySQL 中一定学习使用了 NOT NULLPRIMARY KEYUNIQUE 等约束,PostgreSQL 提供了 CHECK 约束,用户可以使用任意的条件表达式自定义约束。

与多数约束相同,CHECK 约束可以是单一的,也可以是联合的。

1
2
3
4
5
6
CREATE TABLE Employee(
id int,
name text,
gender char(1) CHECK (gender in ('F', 'M')), -- 自定义约束: 性别必须是 'F' 或 'M'
age int
);
1
2
3
4
5
6
7
8
CREATE TABLE Employee(
id int,
name text,
gender char(1),
age int,
CHECK (age >= 18 AND (gender = 'F' AND age <= 55 OR gender = 'M' AND age <= 60))
-- 复合约束: 员工不得是未成年人, 女员工不得大于 55 岁, 男员工不得大于 60 岁
);

TRIGGER 触发器

PostgreSQL 的语法总体上与 MySQL 是相似的,但 PostgreSQL 强制要求在触发器中使用函数,这就导致在 PostgreSQL 中书写简单的触发器是繁琐的。老师是这么解释的,在实际应用中多数触发器会复用相同的函数,在这方面将触发器与函数绑定是合理的。但无论如何当我在作简单的实验时,书写 PostgreSQL 的触发器让我感觉异常繁琐。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建 Trigger 函数
CREATE OR REPLACE FUNCTION TriggerFunctionUpdateID()
RETURNS TRIGGER AS $$
BEGIN
UPDATE A SET id=NEW.id WHERE id=OLD.id;
RETURN NEW; -- 保持更新, RETURN OLD 表示拒绝执行原更新
END;
$$ LANGUAGE plpgsql;

-- 创建 Trigger 并绑定到 Trigger 函数
CREATE OR REPLACE TRIGGER TriggerUpdateID
AFTER UPDATE ON B
FOR EACH ROW
EXECUTE FUNCTION TriggerFunctionUpdateID(); -- 执行函数

事务

默认隔离级别

在 PostgreSQL 中,事务的默认隔离级别是 READ-COMMITTED,而在 MySQL 中事务的默认隔离级别是 REPEATABLE-READ。事实上,除了 MySQL 似乎多数的数据库都是 READ-COMMITTED 的。