如果你看重许可证成本、能在任何操作系统或云上运行,或者需要 PostGIS 之类的扩展,就选 PostgreSQL。如果你已经在使用微软的技术栈,并且需要随之而来的东西:T-SQL、SQL Server Management Studio、Windows 身份验证,以及 SSIS、SSRS 和 SSAS 数据工具,就选 SQL Server。日常 SQL 很接近,区别在于语法细节、锁的默认行为和许可证。
| PostgreSQL 17 | SQL Server 2022 | |
|---|---|---|
| 许可证 | PostgreSQL License,免费 | 商业许可;有免费的 Express 和 Developer 版 |
| 运行平台 | Linux、Windows、macOS、BSD | Windows、Linux |
| 方言 | PostgreSQL SQL、PL/pgSQL | T-SQL |
| 返回被修改的行 | RETURNING | OUTPUT |
| Upsert | INSERT ... ON CONFLICT, MERGE | MERGE |
| 取前 n 行 | LIMIT n, FETCH FIRST n ROWS ONLY | TOP (n), OFFSET ... FETCH |
| 布尔类型 | boolean | bit |
| JSON | json 和 jsonb 类型 | 文本(nvarchar)加 JSON 函数 |
| 数组 | 支持 | 不支持 |
| 聚集索引 | 没有,表是堆表 | 有,默认是主键 |
| 事务中的 DDL | 支持 | 支持 |
返回被修改的行
SQL Server 的 OUTPUT inserted.* 和 OUTPUT deleted.* 在 PostgreSQL 中对应 RETURNING,它适用于 INSERT、UPDATE、DELETE 和 MERGE:
它返回工单 1 和 2,状态已经变为 closed。T-SQL 的写法是:
UPDATE tickets SET status = 'closed'
OUTPUT inserted.id, inserted.status
WHERE status = 'open';
不用 MERGE 的 upsert
SQL Server 用 MERGE 做 upsert。PostgreSQL 也有 MERGE(从 15 版开始,从 17 版开始支持 RETURNING)。更短的 INSERT ... ON CONFLICT 是常用的选择,而且两个会话同时插入相同的键时,它依然正确:
A-1 被更新为 12,B-2 被插入。各种写法见 upsert 页面。
需要改写的 T-SQL 语法
EU 有 1 个已付款订单,US 有 2 个。逐行来看,和 T-SQL 相比变化如下:
| SQL Server | PostgreSQL |
|---|---|
SELECT TOP (10) ... | ... LIMIT 10 |
'Region ' + region | 'Region ' || region |
SUM(CASE WHEN paid = 1 THEN 1 ELSE 0 END) | count(*) FILTER (WHERE paid) |
ISNULL(a, b) | COALESCE(a, b) |
GETDATE() | now() |
[order] | "order" |
#temp 临时表 | CREATE TEMP TABLE |
文本比较也不同。SQL Server 的默认排序规则不区分大小写,而 PostgreSQL 比较文本时区分大小写。WHERE name = 'ana' 不再匹配 Ana,所以要用 ILIKE 或 lower()。
查询 JSON
SQL Server 2022 把 JSON 存成文本,用 JSON_VALUE、JSON_QUERY 和 OPENJSON 读取。PostgreSQL 的 jsonb 是独立的类型,写入时只解析一次,并且有专门的操作符和 GIN 索引:
对应的 T-SQL 用 JSON_VALUE(data, '$.type') = 'purchase' 来筛选。操作符列表见 JSONB 页面。
读、写和锁
PostgreSQL 始终使用多版本并发控制(MVCC):读取方看到的是一个快照,永远不用等待写入方。SQL Server 默认的读已提交级别会加共享锁,所以读取方可能要等待写入方。打开 READ_COMMITTED_SNAPSHOT,或者使用 SNAPSHOT 隔离级别,SQL Server 也会读取行版本。
存储方式也不一样。PostgreSQL 把旧的行版本留在表中,直到 VACUUM 把它们清除。SQL Server 默认把表存成以主键为键的聚集索引。它还有用于分析的列存储索引,而 PostgreSQL 没有内置这种索引。
复制、许可证和工具
PostgreSQL 支持到只读备库的流复制,以及针对选定表的逻辑复制。自动故障转移需要外部工具。SQL Server 提供 Always On 可用性组、日志传送和事务复制。完整的可用性组需要 Enterprise 版。
PostgreSQL 无论规模多大都不收费。SQL Server 2022 Express 在每个数据库 10 GB 以内免费,Developer 版在非生产环境中免费。Standard 和 Enterprise 版要付费。客户端方面,PostgreSQL 有 psql 和 pgAdmin,SQL Server 有 SQL Server Management Studio(仅限 Windows)和 sqlcmd。
常见问题
PostgreSQL 能很好地替代 SQL Server 吗?
对于应用数据库,通常可以:表、连接、事务、存储函数和 JSON 在 PostgreSQL 中都有对应的功能。工作量在于移植 T-SQL 存储过程,以及替换 SQL Server 独有的部分,比如 SSIS 包、时态表或列存储索引。
SQL Server 是免费的吗?
SQL Server 2022 Express 免费但有限制,包括单个数据库最大 10 GB。Developer 版可以免费用于开发和测试,但不能用于生产环境。Standard 版按核心或按服务器加客户端访问许可证收费,Enterprise 版按核心收费。
PostgreSQL 中和 SELECT TOP 对应的是什么?
LIMIT:SELECT * FROM orders ORDER BY id LIMIT 10。PostgreSQL 也支持标准写法 FETCH FIRST 10 ROWS ONLY,SQL Server 在 OFFSET 0 ROWS 之后也支持这种写法。
PostgreSQL 能在 Windows 上运行吗?
能。PostgreSQL 可以在 Windows、Linux、macOS 和各种 BSD 上运行。SQL Server 可以在 Windows 和 Linux 上运行,也可以运行在 Linux 容器中。