Menu

PostgreSQL 和 SQL Server 的区别:该选哪个

PostgreSQL 免费开源;SQL Server 是微软的商业数据库,使用 T-SQL 和以 Windows 为中心的工具链。下面介绍两者在 SQL、JSON、锁和许可证上的区别。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

如果你看重许可证成本、能在任何操作系统或云上运行,或者需要 PostGIS 之类的扩展,就选 PostgreSQL。如果你已经在使用微软的技术栈,并且需要随之而来的东西:T-SQL、SQL Server Management Studio、Windows 身份验证,以及 SSIS、SSRS 和 SSAS 数据工具,就选 SQL Server。日常 SQL 很接近,区别在于语法细节、锁的默认行为和许可证。

PostgreSQL 17SQL Server 2022
许可证PostgreSQL License,免费商业许可;有免费的 Express 和 Developer 版
运行平台Linux、Windows、macOS、BSDWindows、Linux
方言PostgreSQL SQL、PL/pgSQLT-SQL
返回被修改的行RETURNINGOUTPUT
UpsertINSERT ... ON CONFLICT, MERGEMERGE
取前 n 行LIMIT n, FETCH FIRST n ROWS ONLYTOP (n), OFFSET ... FETCH
布尔类型booleanbit
JSONjson 和 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 ServerPostgreSQL
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 容器中。

Coddy 编程语言插图

和 Coddy 一起学编程

开始