目录
SQL Server vs PostgreSQL
要进行高层次的比较，请在此处查看此数据库引擎页面。
并发控制
行数据的并发控制是数据库系统的重要方面，它决定了行数据的维护和更新效率。不同的数据库系统具有不同的并发控制机制，这些机制直接影响其性能。
SQL Server并发控制
传统上，2005之前的SQL Server版本使用行级独占锁，该锁在写操作期间锁定整个行。这样可以防止其他读取和写入线程访问该行，并且它们必须等到该行上的当前写入操作完成（提交或回退）。在大量并发写入和读取期间，这会对数据库的性能产生重大影响。
SQL Server 2005通过引入“行版本隔离级别”改善了这种情况。请参阅锁定和行版本控制。请注意，有两种单独的MVCC实现，使用行版本控制（RCSI）的读取提交隔离和快照隔离（SI）
SQL Server查询可以返回脏（未提交）行，具体取决于事务的隔离级别。
PostgreSQL并发控制
PostgreSQL使用多版本模型（多版本并发控制，MVCC）维护数据一致性，这意味着每个SQL语句都可以像以前一样看到数据快照（一个数据库版本），而不管基础数据的当前状态如何。这样可以防止语句查看由并发事务对同一数据行执行更新而产生的不一致数据，从而为每个数据库会话提供事务隔离。MVCC避免了锁定行，从而最大程度地减少了锁定争用并提高了整体性能。
使用并发控制的MVCC模型而不是锁定的主要优点是，在MVCC中，为查询（读取）数据而获取的锁与为写入数据而获取的锁不冲突，因此读取永远不会阻止写入，而写入永远不会阻止读取。PostgreSQL即使通过使用可序列化快照隔离（SSI）级别提供最严格的事务隔离级别，也保持这种保证。
PostgreSQL绝不会返回脏行，无论事务的隔离级别如何。
数据库结构
与SQL Server一样，PostgreSQL可以在一个实例中包含多个数据库，并且每个数据库可以包含多个模式。这些模式中的每一个都可以包含其他数据库对象，例如表、视图、存储过程、函数等。
在PostgreSQL中打开与数据库的连接时，只能在该连接中引用该数据库。如果您对连接的查询引用了在同一实例上运行的其他数据库，则PostgreSQL将引发错误。您可以分别为每个数据库打开一个连接，也可以使用dblink或Foreign Data Wrappers从多个数据库中查询表。
T-SQL与PL/pgSQL
SQL Server对标准SQL语言的过程扩展简称为Transact-SQL或T-SQL，并提供其他功能，例如过程编程、循环构造、条件构造、会话管理、存储过程等。
PostgreSQL有多种过程语言，默认语言称为PL/pgSQL。
PostgreSQL具有更完善的SQL语法
一旦开始使用PostgreSQL，您会发现与SQL Server相比，它具有更加简洁和完善的语言语法。比较以下语句：
SQL Server
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[customers]') AND type IN (N'U'))
BEGIN
CREATE TABLE [dbo].[customers]( .... )
ENDPostgreSQL
CREATE TABLE IF NOT EXISTS customers ( .... ) PostgreSQL代码更加简单，简洁和完善。SQL Server代码绝对是维护的噩梦。
几个例子：
SQL Server
-- This throws an error if the table doesn’t exist
DROP TABLE [dbo].[customers]
-- This is how you do it in SQL Server 2014 and older
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[customers]') AND type IN (N'U'))
BEGIN
DROP TABLE [dbo].[customers]
END
-- This works in SQL Server 2016 and newer
DROP TABLE IF EXISTS [dbo].[customers]PostgreSQL
ROP TABLE IF EXISTS customers;
-- No error if the table doesn’t exist同样，PostgreSQL支持CREATE OR REPLACE存储过程、函数、视图等的语法。但是在SQL Server 2014及更低版本中，您必须检查对象是否存在，然后将其删除，然后再尝试再次创建它。您可以使用ALTER语句，但是如果该对象尚不存在，它将引发错误。SQL Server 2016 SP1引入了CREATE OR ALTER存储过程、函数、触发器和视图的语法。
SQL Server 2016及更高版本支持DROP IF EXISTS表/视图，但仍不支持CREATE IF NOT EXISTS语法。
对象名称区分大小写
在SQL Server中；对象名Customers, CUSTOMERS和customers都是相同的。SQL Server不会在对象名称中强加区分大小写，并且会创建与CREATE 语句中指定的大小写相同的对象。
在PostgreSQL中，对象名称被静默转换为小写。当名称用双引号引起来时，该名称区分大小写，并且在查询中必须与引号一起使用。因此，上述名称将转换为customers，但“Customers”（带引号）将被视为不同的对象，在引用该对象的查询中必须将带引号的对象也视为不同的对象。此规则也适用于表中的列名。
数据库对象/特征
将数据库对象从SQL Server迁移到PostgreSQL非常简单，因为大多数对象在目标平台中均受支持。
| 对象/功能 | SQL Server | PostgreSQL | 评论 |
| 表 | 是 | 是 | |
| 索引 | 是 | 是 | |
| 触发器 | 是 | 是 | |
| 视图 | 是 | 是 | |
| 可更新的视图 | 是 | 是 | |
| 物化视图 | 是 | 是的，从v9.3开始 | |
| 计算列 | 是 | 是的，在v12使用视图的早期版本中可用 | 计算列在PostgreSQL中称为生成列 |
| 存储过程 | 是 | 是的，从v11开始 | 在PostgreSQL 11之前，开发人员使用函数而不是存储过程 |
| 用户定义功能 | 是 | 是 | |
| 重载功能 | 没有 | 是 | 在PostgreSQL中，您可以具有名称相同但参数不同的多个函数。 |
| 公用表表达式（CTE） | 是 | 是 | PostgreSQL 11及更早版本：CTE总是物化，并且可能性能不佳。PostgreSQL 12：查询提示允许CTE物化或不物化。 |
| 函数索引 | 没有 | 是 | |
| 最大表大小 | 无限 | 在PostgreSQL 9.6或更早版本中为32 TB（32TB）在PostgreSQL 10中为2 Exabytes（2EB） | |
| 每张表的最大列数 | 1024（non-wide）30000（wide） | 250 – 1600，取决于列类型 | |
| 最大数据库大小 | 524,272 TB | 无限 | |
| 最大Varchar长度 | Varchar（Max） | Varchar（10485760）将文本用于较长的字符串 | |
| 调度器 | SQL Server代理 | pgAgent |
数据类型
无论使用工具还是使用手动方法，在进行迁移时，请在SQL Server和PostgreSQL数据类型之间保留以下数据类型映射。一些工具使您可以研究数据类型映射，以微调迁移过程。
| Microsoft SQL Server | 描述 | PostgreSQL |
| Text | ||
| CHAR(n) | 固定长度的字符字符串，1 <= n <= 8000 | CHAR(n) |
| VARCHAR(n) | 可变长度的char字符串，1 <= n <= 8000 | VARCHAR(n) |
| VARCHAR(max) | 可变长度字符字符串，<= 2GB | TEXT |
| NVARCHAR(n) | 可变长度Unicode UCS-2字符串 | VARCHAR(n) |
| NVARCHAR(max) | 可变长度Unicode UCS-2数据，<= 2GB | TEXT |
| TEXT | 可变长度字符数据，<= 2GB | TEXT |
| NTEXT | 可变长度Unicode UCS-2数据，<= 2GB | TEXT |
| UNIQUEIDENTIFIER | 16字节的GUID（UUID）数据 | CHAR(16) |
| Numeric | ||
| BIGINT | 64位整数 | BIGINT |
| INTEGER | 32位整数 | INTEGER |
| TINYINT | 8位无符号整数，0到255 | SMALLINT |
| DOUBLE PRECISION | 双精度浮点数 | DOUBLE PRECISION |
| FLOAT(p) | 浮点数 | DOUBLE PRECISION |
| NUMERIC(p,s) | 定点数 | NUMERIC(p,s) |
| SMALLMONEY | 32位货币金额 | SMALLMONEY |
| Date | ||
| DATE | 日期包括年，月和日 | DATE |
| DATETIME | 带分数的日期和时间 | TIMESTAMP(3) |
| DATETIME2(p) | 带分数的日期和时间 | TIMESTAMP(n) |
| DATETIMEOFFSET(p) | 带有分数和时区的日期和时间 | 带时区的TIMESTAMP(p) |
| SMALLDATETIME | 日期和时间 | TIMESTAMP(0) |
| Boolean | ||
| BIT | 1、0或NULL | BOOLEAN |
| Binary | ||
| BINARY(n) | 固定长度的字节字符串 | BYTEA |
| VARBINARY(n) | 可变长度的字节字符串，1 <= n <= 8000 | BYTEA |
| VARBINARY(max) | 可变长度的字节字符串，<= 2GB | BYTEA |
| ROWVERSION | 自动更新二进制数据 | BYTEA |
| IMAGE | 可变长度的二进制数据，<= 2GB | BYTEA |
该列表仅包含常用的数据类型，PostgreSQL支持多种数据类型，还支持自定义数据类型。有关PostgreSQL支持的主要数据类型的完整列表，请查看此文章。
内置功能和运算符
PostgreSQL具有丰富的运算符和内置函数集，远远超出了SQL Server提供的功能。
| Microsoft SQL Server | PostgreSQL | 评论 |
| DATEPART | DATE_PART | |
| ISNULL | COALESCE | |
| SPACE | REPEAT | |
| DATEADD | + | |
| + | || | 字符串串联 |
| CHARINDEX | POSITION | |
| GETDATE | NOW | |
| LTRIM/RTRIM | TRIM | |
| REPLACE | OVERLAY | |
| LEN | OCTET_LENGTH |
有关所有PostgreSQL函数和运算符的完整列表，请查看此页面。
SQL语言差异
| 特征 | Microsoft SQL Server | PostgreSQL |
| 选择前N行 | TOP n | LIMIT n |
| 语句终止符 | ; （不需要） | ; （需要） |
| LIKE 运算符 | 默认情况下不区分大小写（由排序规则确定） | 区分大小写，使用ILIKE进行不区分大小写的比较 |
| 正则表达式 | LIKE运算符 | SIMILAR TO运算符 |
CTE性能差异
在SQL Server中，此查询：
WITH AllUsers AS (SELECT * FROM Users)
SELECT * FROM AllUsers WHERE Id = 100; 一次生成整个查询的查询计划，并且WHERE子句过滤器传递到CTE中。生成的查询计划是高效的，仅执行单个聚集索引查找即可。
在PostgreSQL中，CTE是优化屏障（外部查询限制不传递给CTE），数据库在CTE内部评估查询并缓存结果（即物化结果），并且稍后在处理外部查询时应用外部WHERE子句，这意味着将执行全表扫描或全索引查找，这会导致大型表的糟糕性能。为了克服这个问题，您可以在PostgreSQL中将该查询重写为：
WITH UserRecord AS (SELECT * FROM Users WHERE Id = 100)
SELECT * FROM UserRecord; 另一个选择是使用子查询重写查询。在迁移涉及CTE的代码和查询时，请记住这一点。
请注意，PostgreSQL 12通过引入查询优化器提示来解决此问题，以使我们能够控制CTE是否应物化：MATERIALIZED，NOT MATERIALIZED。因此，可以将查询重写如下，以获得更好的性能。
WITH AllUsers AS NOT MATERIALIZED (SELECT * FROM Users)
SELECT * FROM AllUsers WHERE Id = 100; Collation / Ordering
SQL Server和PostgreSQL存储和比较数据的方式几乎没有根本差异。在SQL Server中，默认排序规则区分大小写，而在PostgreSQL中，默认排序规则区分大小写。因此，在联接或LIKE语句中的WHERE子句或ON子句中搜索文本的代码/查询可能会失败。PostgreSQL提供了一个不区分大小写的比较ILIKE语句。
当删除不删除时
在SQL Server中，当对表发出DELETE语句时，行将从存储中永久删除。在PostgreSQL中，它们只是标记为删除（软删除），而不会立即从存储中删除。UPDATE语句的行为类似，将旧行标记为删除，并将新行与新行数据一起插入。
在迁移定期进行大量删除和更新的表时，请记住这一点，因为在执行清理(VACUUM)之前，它们将呈指数增长。您将必须设置一个工作，以定期清理数据库中的表，该间隔可以每天，每周或每月一次，具体取决于每个表的增长率。
该自动清理后台程序在默认情况下开启，并确保清理定期自动完成的，因此您可能不会在大多数情况下担心。但是，它可以在安装过程中关闭，也可以稍后由数据库管理员关闭。如果将PostgreSQL数据库托管在AWS Aurora中，则即使在运行自动清理(AutoVacuum)守护程序时也要小心。查看此帖子以获取更多信息。
有关更多详细信息，请参见VACUUM语句。
PostgreSQL有优势的地方
PostgreSQL提供了一组丰富的运算符和函数来处理不同类型的数据。
PostgreSQL实际上可以在任何地方运行
PostgreSQL在Windows，Linux，Unix等上运行，而SQL Server 2016和更早版本仅在Windows上运行。SQL Server 2017也可以在Linux上运行。
将测试数据插入PostgreSQL表很容易
此查询使用随机数据将一百万行插入到customers表中。
INSERT INTO customers (id, name)
SELECT id, md5(random()::text)
FROM generate_series(1, 1000000) AS id; 在SQL Server中，您将只能使用过程代码来执行此操作。
PostgreSQL中的多语言支持
当前在标准PostgreSQL发行版中有四种可用的过程语言：PL/pgSQL，PL/Tcl，PL/Perl和PL/Python。PostgreSQL也支持许多其他过程语言，您只需要安装适当的扩展并将其启用即可。
- PL/pgSQL：此PostgreSQL的本地过程语言类似于SQL Server的T-SQL，但更加完善和功能齐全。
- PL/Python：PostgreSQL对Python的支持为您的武器库增加了庞大的Python库生态系统。想象一下，在SQL查询中间使用Python函数Yayy ！！！
- PL/Perl：PostgreSQL完全支持Perl作为过程语言。
- PL/Tcl：PL/Tcl是PostgreSQL数据库系统的可加载过程语言，它使Tcl语言可用于编写PostgreSQL函数。
- PL/V8：JavaScript引擎稳定，功能丰富且非常快速。PostgreSQL本身支持JSON数据类型，这一事实使其功能强大且灵活。PL/V8支持在内存中缓存数据，这使其成为数据密集型行级操作的理想语言。
- PL/R：PostgreSQL完全支持R，R是一种统计编程语言，广泛用于数据科学中，具有一组强大的高质量插件和附加组件。
C：尽管PostgreSQL支持C，但它必须单独编译。当对内存管理的速度和精细控制，任务的资源使用和性能至关重要时，这非常有用。
PostgreSQL还具有其他语言的扩展，例如Java，Ruby，PHP，Lua，Tcl等。
有人可以说SQL Server也支持多种语言，因为它充当.NET主机，并且可以运行以任何.NET语言（如C＃，VB.NET，F＃等）编写的代码。是的，但是涉及到在.NET中编写代码。像Visual Studio这样的IDE，编译代码，在SQL Server中部署程序集，为C＃函数编写包装T-SQL函数，然后从T-SQL代码中调用该函数。更不用说调试地狱了。您不能像PostgreSQL中的LANGUAGE关键字允许您用多种语言编写函数那样，在T-SQL代码内“内联”编写C＃代码。
在SQL Server中亲自完成多语言编程之后，我可以保证PostgreSQL提供的多语言支持级别与SQL Server处于不同级别。
PostgreSQL具有丰富的函数和运算符集
GREATEST函数与MAX函数相似，不同之处在于GREATEST函数可以跨列工作。而LEAST函数类似MIN函数，但它可以跨列。
SELECT GREATEST(LastLoginDate, LastReportDate, LastInteractionDate) FROM Users;您不能在SQL Server中执行此操作，除非有多个SELECT语句和过程结构。同样，PostgreSQL中有大量功能可用，而SQL Server中不可用。
PostgreSQL对CSV有更好的支持
每天处理数据的人经常会使用CSV来将数据从一种格式转换为另一种格式并进行分析处理等。PostgreSQL的COPY FROM和COPY TO命令在处理CSV方面做得更加干净：文本被截断，不再进行编码噩梦，不再引用/转义问题。
您可以使用单个语句在PostgreSQL中删除整个模式
在PostgreSQL中，您要做的就是执行DROP SCHEMA CASCADE。这在开发过程中非常有用，但同时又在过渡和生产环境中冒着地狱般的危险。
在SQL Server中，DROP SCHEMA不支持CASCADE子句。必须先删除架构中的所有对象，然后再删除架构，这可能是开发和原型制作过程中的噩梦。
Unicode 和字符编码基础
Unicode是字符编码标准，而UTF-8和UTF-16是该标准的不同实现。
UTF-8：UTF-8使用1个字节表示低于128的字符代码，并使用2、3或4个字节表示超出此范围的字符。与ASCII兼容。
UTF-16：始终使用两个或四个字节，与ASCII不兼容
UCS-2：始终使用两个字节。
Unicode支持
PostgreSQL对UTF-8编码具有本机支持，默认情况下其CHAR，VARCHAR和TEXT类型为UTF-8。字符串操作和正则表达式均支持UTF-8。PostgreSQL不支持UTF-16。
SQL Server（2012年之前）仅支持UCS-2（UTF-16的子集）。SQL Server 2012引入了对UTF-16的可选支持，但是您必须为数据库选择UTF-16排序规则才能使其工作。SQL Server不支持UTF-8。
SQL Server有优势的地方
您不能在单个查询中直接查询多个数据库
与SQL Server不同，您不能直接在查询中引用两个数据库。但是有一种解决方法，您可以使用dblink或Foreign Data Wrapper。
您不能直接在PostgreSQL中执行过程代码
在SQL Server中，可以在脚本中声明变量或直接使用条件逻辑：
DECLARE @MeanAge = 100
IF EXISTS (SELECT * FROM Users WHERE Age > @MeanAge)
SELECT 'Yay'
ELSE
SELECT 'Nay'; SQL Server将允许您使用客户端工具或在应用程序代码中的内联查询中运行此代码。在PostgreSQL中，您可以运行以下程序代码：
DO $$
-- declare
BEGIN
/* pl/pgsql here */
END $$;但是，您无法将任何结果返回给客户端。您可以运行过程代码并将结果返回给客户端的唯一方法是将其包装在存储过程或函数中，然后执行它。
PostgreSQL 不支持版本11之前的存储过程
PostgreSQL 10.x及更低版本没有存储过程。但是，这并不是一件容易的事，因为您可以使用用户定义的函数来完成所有操作，唯一的例外是您不能在函数内部进行事务处理。
PostgreSQL在版本12之前不支持计算列
PostgreSQL 11.x及以下版本不支持计算列。但是，计划于2019年末发布的PostgreSQL 12引入了对'Generated column'的支持。看看这个文章以获取更多信息。
5716
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
