PostgreSQL vs. MS SQL Server

从一个数据分析师的视角来对比两个关系型数据库。

0.本文是关于什么的？

我在一个全球专业服务公司做数据分析师（你肯定听说过的）。我干了大概有10年。10中我处理数据、数据库软件、数据库硬件、数据库用户、数据库程序员以及数据分析方法，所以我对这些东西了解的比较多。我经常遇到对相关内容了解很少的人，虽然他们中的一部分并没有意识到这件事。

这些年里，我已经太多太多次的讨论了 PostgreSQL 和 MS SQL 的问题。IT 行业中一个知名的原则说：如果你准备不只一次的做同一件事，那就让它自动化。本文是我的自动化方法的谈话。

除非另有说明，我指的是PostgreSQL 9.3和MS SQL Server 2014，即使我的经验是在MS SQL Server 2008 R2和2012版。为了公平起见，我将比较最新版的MS SQL Server和PostgreSQL。由于微软的糟糕的文档，我不得不大量的依赖于Google、Stack Overflow以及网络上的用户。因为我对两个数据库的经验不相等，所以我知道像这样的比较不够科学严谨。不过这不是一个学者的练习题，这是现实中的比较。我尽可能让我对于MS SQL Server的了解正确，因为我们都知道要糊弄整个互联网是不可能的。如果我发现我弄错了什么事情，我会修正的。

我将以一个数据分析师的角度来比较两个数据库。MS SQL Server可能会因为QLTP后台而踢PostgreSQL的屁股（虽然我比较怀疑），不过那些不是我这里要关注的，因为我不是一个OLTP开发者/DBA/系统管理员。

最后，右上角有一个email地址。如果你愿意的话你会用到的，我会尽可能回复的。

免责声明：本文所有观点仅代表我个人。

1. 为什么说 PostgreSQL 比 MS SQL Server 强的多

额，剧透了。本节从数据分析的角度对比这两种数据库。

1.1. 支持 CSV

CSV 其实是转移结构化数据(如: 表)的一种标准方式。不论是哪一种数据库，都能用自己专有的格式，把数据导出来。以这种格式存储的数据，其他软件无法读取. 用来做备份或者复制数据还行。如果想从 X 系统, 把数据移植到 Y 系统，那问题就大了。

一个数据分析平台, 既要能读取不同系统的数据, 也要能生成其他系统能读取的分析结果. 也就是说, 要能快速, 稳定, 可重复的, 而且毫无痛苦的读写 CSV. 我再说一次：一个不能很好的处理 CSV 的数据分析平台，就是没用的累赘。

PostgresSQL对CSV的支持在业内是顶尖的。 COPY TO和 COPY FROM命令支持RFC4180（最接近官方标准的文档）中列出的所有规格，也支持很多常见的和不常见的变种和方言。这些命令运行速度很快而且很强大。发生一个错误时，它们会给出有帮助性的错误信息。更重要的是，它们不会默默地损坏、误解、修改数据。

而MS SQL Server既不支持导入也不支持导出CSV文件。很多人不相信当我告诉他们这一点时。然后，某一次，他们自己验证了这一点。通常他们的观察是这样的：

MS SQL Server默默地清除（truncate）了一个文本字段的数据
MS SQL Server对文本进行编码时发生错误
MS SQL Server抛出一个错误信息因为它不理解引用或转义（出乎人们的意料，对CSV来说引用和转义不是特殊的扩展。从字面上看，引用和转义是每一个人类可读的数据序列化规范的基本概念。不要相信那些不懂这些东西的人。）
MS SQL Server导出损坏的、不可用的CSV文件
微软有一篇惊人的文档。他们怎么能把CSV这么简单的东西如此复杂化的呢？

如果你不相信，下载这个格式正确的、符合标准的UTF-8编码的CSV文件，用MS SQL Server计算文件中最后一列（共有50列）字符串的平均长度（或者是字符的数量，等等）。继续，试一下。

（你得到的答案将是 183.895。）

当然，事实上，对 PostgreSQL 来说，确定这么做非常简单。最耗费时间的地方是创建保存这些数据的且具有50个字段的数据表。微软本身似乎就很难理解CSV文件；而且打开这样的文件还会引起Access和Excel中断。

痛苦但却是事实的情况是：我了解到近期一些数据库编程人员花费大量的时间和精力编写Python代码，以实现对CSV文件的“清理”，从而让MS SQL服务器可以把这些文件的内容导入到数据库里。但是，这种处理方法不可避免的要更改实际的数据。这就像花费大量金钱购买了Photoshop，然后不得不编写一些定制的代码来让Photoshop打开JPEG，到头来仅仅发现只是稍稍修改了图片那样让人抓狂。

1.2.人机工程

值得一提的是每个数据分析平台都是图灵完备的，这大概意味着任何一个数据分析平台可以做其他数据分析平台做的任何事情。也就不存在“你可以在A软件中做X这件事而不可以在B软件中做X这件事”。即你可以在任何软件里做任何事情-所不同是难易程度。好的工具让你要做的事情做起来非常简单；差的工具就会让你要做的事情做起来很难。说到底就是这么回事。

（理论上来讲这一切都是正确的，然而现实中却不是这样的-例如，我了解到没有关系型数据库管理系统(RDBMS)使用3D图形。不过，任意一个关系型数据库管理系统都可以模拟GPU执行任何图形计算。）

很显然，PostgreSQL 是由实际关心如何对资料进行处理的人编写的。而MS SQL服务器则是由那些根本就不需要使用MS SQL服务器来实现某件事情的人编写的。下面的几个例子就可以说明：

PostgreSQL支持DROP TABLE IF EXISTS，这是一个非常聪明且显而易见的处理方式，它表明：“如果这个表不存在，就什么也不做，如果存在，就删除它”。例如：
```
DROP TABLE IF EXISTS my_table;
```
在MS SQL服务器中，你却需要这么做：
```
IF OBJECT_ID (N'dbo.my_table', N'U') IS NOT NULL  DROP TABLE dbo.my_table;
```
是的，只是多了一行代码，不过要留意OBJECT_ID函数中令人奇怪的第二个参数。删除视图时你需要把它替换为N'V'。删除存储过程时替换为N'P'。我不知道所有不同类型的数据库对象对应的各个字符（为什么我必须要知道呢？）还要注意到另一点：不必要地重复了表名。你稍不留意，就容易做出以下事情：
```
IF OBJECT_ID (N'dbo.some_table', N'U') IS NOT NULL  DROP TABLE dbo.some_other_table;
```
看看此时会发生什么？这确实是令人懊恼、浪费时间的错误。

PostgreSQL支持DROP SCHEMA CASCADE，它会删除模式以及该模式下的所有数据库对象。对一个强壮的分析方法来说，做到这一点非常、非常重要，因为此时分割和重建是进行可重复的、可审计的协作分析工作的基本操作方法。而MS SQL服务器却不是这样的。你不得不手工删除该模式下的所有对象，而且要按照正确的顺序删除，因为在你试图删除一个其他对象依赖的对象时，MS SQL服务器只会抛出一个错误。这使得整个处理过程非常笨拙。

PostgreSQL 支持 CREATE TABLE AS。一个简单的例子如下：
```
CREATE TABLE good_films AS  SELECT    *  FROM    all_films  WHERE    imdb_rating >= 8;
```
这就意味着你可以使用除第一行以外的其他行，并执行，在开发SQL代码时，这是一个常见的且非常有用的处理方式。在MS SQL服务器里，你要采用如下代码才能以上面的方式创建表：
```
SELECT    *  INTO    good_films  FROM    all_films  WHERE    imdb_rating >= 8;
```
此时，要执行普通的SELECT语句的话，你需要注释或者删除INTO部分。是的，注释两行非常简单；不过这不是我们关注的地方。我们关注的是在PostgreSQL里，你不需要修改代码就可以执行这个简单的任务，而在MS SQL服务器上，你无法做到这一点，而且你要做到这一点还会带来另一个潜藏的漏洞和令人讨厌的东西。

在PostgreSQL里，你可以在一次批处理里执行你愿意执行数量级的SQL语句；只要每个语句都以分号结束，你就可以执行你所想到的任何语句组合。对于哪些要执行自动批处理、构建重复数据或者进行输出的程序来说，这是一个非常重要的功能。在MS SQL服务器里，在一个批处理的SQL语句中间不能出现CREATE PROCEDURE语句。这么做没有任何好的理由，仅仅是随意加的一个限制。此时就意味着需要额外的手工操作来执行大量的SQL批处理。手工操作会增加风险，降低效能。
PostgreSQL 支持 RETURNING 子句，允许 UPDATE,INSERT 和 DELETE 语句返回已更改行上的数据值。这么做非常简洁有益。MS SQL 服务器有个 OUTPUT 子句可满足这方面需求，不过它需要单独定义表变量来实现此功能。这么做很笨拙而且不方便，还迫使程序开发人员创建并维护不怎么需要的代码。

PostgreSQL 支持用 $$ 将字符串括起来, 像这样:
```
SELECT $$Hello, World$$ AS greeting;
```
这样写，对动态生成 SQL 语句很有用，因为 (a) 当嵌入字符串时, 能避免既繁琐, 又容易出错的手工引用和对特殊字符的转义. (b) 由于文本编辑器和 IDE 一般不把 $$ 当作字符串分隔符, 动态生成的 SQL 语句依然根据语法高亮显示。
PostgreSQL 允许你向数据库引擎提交面向过程的编程语言代码; 你可以使用, 像 Python , Perl , R 或 JavaScript, 或其他已被支持的语言(具体看下面), 在同一个脚本文件的 SQL 语句旁边, 加上面向过程的代码. 这样做简洁方便, 易于维护. 同时也方便查看代码, 重复使用, 等等各种好处.

而 MS SQL Server, 你可以使用笨拙, 缓慢, 还有点尴尬的 T-SQL, 或者用 .NET 生成组件(Assembly) 然后加载到数据库中. 。也就是说，你的代码存在两个不同的地方。你得在各种图形界面之间切来换去的修改这些代码。想要将这些东西统一打包放在一起，困难重重。而且也容易出错。

诸如此类例子还有很多. 这些问题, 分来开看, 好像没什么. 可是放到一起, 问题就大了. 想要在 MS SQL Server 上面做好一件事, 要比在 PostgreSQL 做的难度大的多. 数据分析师把许多宝贵的时间和精力都花在, 解决各种问题, 手工处理的过程中, 而不是解决真正需要解决的问题.

更正: 有人跟我说, MS SQL Server 有个优势, 是 PostgreSQL 不具备的. 那就是在 SQL 脚本中声明变量. 如:

DECLARE @thing INT = 1;    SELECT @thing + 6;   --returns 7

PostgreSQL 确实不能声明变量. 真心希望它能加上这个实用的功能.

你能够在Linux,BSD等平台上运行PostgreSQL（当然，在Windows上也可以）

IT行业的开发人员都清楚跨平台是当今一个关注点。支持跨平台可以说是Java杀手级的特性，其实Java是一门尚显粗糙、丑陋的编程语言，但它依然获得了巨大的成功，广泛的影响及普及。Linux与苹果的崛起使微软在桌面领域无法再保持垄断地位。云服务的灵活性和高性能虚拟化技术的易访问性，使IT基础设施越来越多样化。跨平台软件能够提供给用户控制他们的基础设施。（工作中，我目前管理着好几个PostgreSQL 数据库，一些运行在Windows平台上，一些在Ubuntu Linux上。我和同事自由地在这些平台之间移动代码和数据库数据。我们使用Python和PHP，因为他们在两种操作系统上都能运行。它们全部运行得很好。）

微软的政策一直都是供应商锁定。他们不开放自己的代码；他们不提供跨平台版本的软件；他们甚至自己创造一个完整的生态系统，.NET设计用于为微软用户和非微软用户搭建了一座桥梁。这对他们是有利的，因为这种方式保证了他们的利润。这对你（用户）是不利的，因为微软限制了你的选择，而且为你创建了一些不必要的工作。

这不是一篇对比 Linux和 Windows的文档，尽管我确定我最后会提到几点。可以肯定地说，对于真正的 IT工作， Linux（和类 UNIX操作系统家族： Solaris， BSD等）把 Windows甩出几条街。类 UNIX操作系统主宰着服务器市场、云服务、超级计算（在这个领域它近乎垄断）和科学计算，一个原因就是 - 这些系统是技术人员为技术人员设计的。最终，他们以巨大的力量和灵活性换得了用户友好性。一个合格的类 UNIX OS 不仅仅是一个漂亮的命令行集合 – 它是一个包含各种程序、实用工具、功能的生态系统，并且提供支持使完成工作变得高效和有趣。一个合格的 Linux黑客可以用一行被抛弃的 Bash脚本达到目的，但是这个任务在 Windows中是艰巨且耗时的。

（例如，某一天，我在查看朋友影片收集情况，他对我说，他认为他的文件系统中文件的总数量太多，他想知道究竟有多少影片文件，还想知道他是否可还以把一个大型的文件夹结构复制到影片文件夹下。我使用下面语句对每个文件夹及其子文件夹所包含文件数进行了计算：

find . -type f | awk 'BEGIN {FS="/";} {print $2;}' | sort | uniq -c | sort -rn | less

整件事情做下来编写花了一分钟，运行花了一秒钟。同时还证实某些文件夹有问题，并告诉他具体哪个文件夹有问题。Windows下怎么能做到这些呢？）

在做数据分析时，关系型数据管理系统(RDBMS)不可能处在真空里；它是整套工具中一部分。因此它所处的环境就非常重要。MS SQL服务器只可在Windows系统上使用，而Windows是一个很差劲的可用于分析的环境。

1.4程序语言特性

这可是一个大问题。

一个“纯”字可以概括SQL，因为它只专注于它被设计的初衷，那就是关系型数据的操作和查询。如果你尝试用它做更多的分析处理的话，比如复杂的利息计算、时间序列分析以及通用算法设计，你将很快达到它的极限。SQL数据库的提供者对这些比较了解，所以几乎所有的SQL数据库都实现了某种程序语言。这就是使得数据库用户可以写命令式风格的代码以用于更复杂或繁琐的任务。

PostgreSQL的程序语言支持比较好。对它来说在一个小范围内是不可能做到公正的，不过这只是一个样本。这些程序语言的任何一个都可以用来写存储过程和函数或直接转储到一个内联执行的代码块。

PL/PGSQL：它是PostgresSQL 原生的程序语言。它与Oracle的PL/SQL类似，但是它比后者更现代、功能更完善。
PL/V8：来自Google Chrome的V8脚本引擎在PostgresSQL中可以使用。这个引擎稳定、功能丰富、速度不可思议地快 – 经常接近于经过优化和编译的C语言代码的执行速度。把它和PostgresSQL对JSON数据类型的原生支持结合起来（见下文），在一个单独的包中你会有终极的力量和灵活性。甚至更好，PL/V8支持全局（跨函数调用，等等）状态，允许用户选择性地快速随机访问RAM中高速缓存数据。假设表A有100,000行数据，表B有1,000,000行数据，对于表B中的每行数据都需要与表A中所有数据进行关联。使用传统的SQL，你要么需要关联这两张表（在中间表中会有10^11行数据，这会累死所有计算机，除最强大计算机外）或者进行一个类似于标量的子查询（或者使用基于游标的嵌套循环，这更糟），如果查询解析器没有正确地读懂你的意图，这些方法会带来沉重的I/O负担。在PL/V8中，你只需在内存中缓存表A的数据，遍历表B的数据时调用一个函数 – 访问这个有100,000行的表A时，它会给你RAM级别的访问速度（微不足道的延迟和随机访问惩罚；没有非易失性I/O加载）。最近我在我的一个项目中使用了这种方法- 我的PostgreSQL/PLV8代码运行速度比微软T-SQL解决方法快80倍，并且代码更简洁、更易维护。由于运行耗时23秒而不是30分钟，我因此能够在1个小时内完成20个测试周期（运行-测试-修改），从而写出功能完善、经过测试、无bug的代码。关于这一点，在这里可以看到更多细节。（由于使用 DROP SCHEMA CASCADE，并且在批量声明中间可以随意执行CREATE FUNCTION语句，所有那些测试周期才是可能完成的，正如上面解释的那样。看它们在一起是多么搭配啊！）

PL/Python:你可以在PostgreSQL里随意使用Python。Python2或者Python3，随你选，都可以，你可以使用大量的Python库，Python正是因为它们才出名的。在SQL查询语句中间想调用scikit-learn提供的SVM或者gmpy2提供的某些任意精度的算法，可以吗？没问题！
PL/Perl：Perl已经不流行一段时间了，不过因其功能多而赢得了编程语言瑞士军刀的美誉。在PostgreSQL里，你完全可以把Perl用做编写存储过程语言。
PL/R：R是学术和数据科学领域统计编程的事实标准，而且还有很好的理由使用它-它免费、强大、功能全面，同时有大量高质量的插件和附件库支持。因此PostgreSQL允许你可以使用R做为编写存储过程的语言。
在PostgreSQL里，还可以用Java、Lua、sh、Tcl、Ruby和PHP做为编写存储过程的语言。
C:它确实不属于存储过程语言列表，因为你需要单独对它进行编译，不过在这儿值得一提。在PostgreSQL中，可以非常容易地创建在数据库后台进程里进行C（或者C++或者汇编) 的编译、优化的函数。这是一个非常强大的用户功能，它可以让哪些对性能有特别要求的任务运行速度最快、内存管理和资源利用得到精确地控制。我已经用它实现了复杂的支付状态处理算法，可在一秒钟操作一百万行数据-而且是在桌面PC上运行的。

MS SQL Server 内置的面向过程编程语言 (T-SQL 对 SQL 扩充的一部分) 既笨拙，又缓慢，各种缺点。就如 Microsoft 自己的文档说的那样，它有时会容易产生一些奇怪的错误和 Bug。我还没见过哪个程序员说他喜欢 T-SQL 的。

那放到 MS SQL Server 上面用的 .NET 组件呢 ? 这种不算面向过程语言支持，因为你不能直接向数据库引擎提交代码。要知道，可管理性和人类工程学（ergonomics ）都很重要。直接将 Python 代码嵌入数据库查询语句中，既简单又方便；启动 Visual Studio，然后管理一堆项目，复制一堆 DLL 文件 (都是在图形用户界面中处理的，不能很好的脚本化，版本控制，自动化，以及审查 )其实挺尴尬的，而且容易出错，扩展性又不好。总之，这种机制在很大程度上受限于.NET 语言。

1.5支持原生正则表达式

正则表达式（regexen或者regexes）对于分析工作来说就像会算术一样的基础，对于大量的文本处理任务来说它们是首选（经常是唯一选择）。不支持正则表达式的数据分析工具就像一个没有座的自行车一样，你仍然可以用它，但是充满痛苦（菊花都残了当然痛—译者加）。

PostgreSQL有开箱即用的正则表达式支持。看几个例子：

取得所有以重复数字并且紧跟元音字母开头的行：

SELECT * FROM my_table WHERE my_field ~ E'^([0-9])\\1+[aeiou]';

取得某一个字段中第一个出现的单独的十六进制字符串：

SELECT SUBSTRING(my_field FROM E'\\y[A-Fa-f0-9]+\\y') FROM my_table;

将一个字符串以空白字符分割，并且以单行的形式返回每一部分：

SELECT REGEXP_SPLIT_TO_TABLE('The quick brown fox', E'\\s+');  -- Returns this:    -- | column |  -- -----------  -- | The    |  -- | quick  |  -- | brown  |  -- | fox    |

查找一个字符串中最少有10个字母的单词（不区分大小写）：

SELECT REGEXP_MATCHES(my_string, E'\\y[a-z]{10,}\\y', 'gi') FROM my_table;

MS SQL Server有 LIKE ，SUBSTRING，PATINDEX 等等，不过它们与恰当的正则表达式支持不具可比性（如果你对此怀疑，你可以尝试使用它们来实现上面的例子）。有第三方的库可用于MS SQL Server，它们不像PostgreSQL的支持那样好，并且获取和安装它们会增进管理开销。

还要注意到PostgreSQL的支持扩展程序语言特性也让你有好几个其他的正则表达式引擎可用，当然也包括它们的各种特性。比如Python的正则库提供的对正向和负向后行断言的支持。这正符合PostgreSQL的一贯作风，给你干好工作的所有你需要的工具。

1.6自定义聚合函数

这是一个PostgreSQL和MS SQL Server两者都提供的一个技术上的特性。不过，在实现上却有巨大的不同。

在PostgreSQL中，自定义聚合很方便并且使用简单，产生了可以快速解决问题和可维护的代码：

CREATE FUNCTION interest_sfunc(state JSON, movement FLOAT, rate FLOAT, dt DATE) RETURNS JSON AS  $$  state.balance += movement;  //payments into/withdrawals from account  if (0 === dt.getUTCDate()) //compound interest on 1st of every month  {    state.balance += state.accrual;    state.accrual = 0;  }  state.accrual += state.balance * rate;  return state;  $$ LANGUAGE plv8;    CREATE AGGREGATE interest(FLOAT, FLOAT, DATE)  (    SFUNC=interest_sfunc,    STYPE=JSON,    INITCOND='{"balance": 0, "accrual": 0}'  );    --assume accounts table has customer ID, date, interest rate and account movement for each day  CREATE TABLE cust_balances AS  SELECT    cust_id,    (interest(movement, rate, dt ORDER BY dt)->>'balance')::FLOAT AS balance  FROM    accounts  GROUP BY    cust_id;

简单吧？自定义的聚集函数主要关注的是内部的状态和我们输入新值给这个聚集函数时修改这个状态的方法。在这个例子里，我们假设一开始每个客户的余额为零，而且累计利息也为零，接着我们每天适当地进行利息累计，并对每天的支付和撤消记账。在每个月的1号，我们进行利息复合。注意：这个聚集函数接纳ORDER BY子句（因为它与SUM、MAX和MIN不同，它是排序相关的），另外PostgreSQL有操作符可以从JSON对象里提取对应的数值。因此，我们在28行代码里创建了每月对银行账户进行利息复合的框架，使用这个框架就可以计算出最终的账户余额。如果打算给这个方法增添功能（比如根据借/贷额度进行利率修改，异常检测），这一切都可以在转换函数里实现，只要采用适合于实现复杂逻辑的语言编写即可。（不幸的是：我看到许多组织机构使用较差的工具花费数万英镑经过数周的努力试图完成同样的任务。）

而MS SQL 服务器要实现这样的任务就异常困难。

顺带说明一下，上面的第二个链接里的例子实现了简单的字符串连接聚集。注意：实现如此简单的功能需要大量的代码和技巧（而PostgreSQL内部提供了此功能，随拿随用。这可能是因为这个功能有用！）MS SQL服务器还禁止在聚集函数里指定排序，使用这样的函数无法完成我现在要完成的任务-在MS SQL服务器里，字符串连接的顺序是随机的，因此使用这个函数查询的结果就是无法确定的（即每次运行结果都可能不同），而且这样的代码也不会通过质量审查的。

缺乏排序支持还可能使得以前编写的代码无法运行，比如上面计算利息的例子。正如我所说，你无法通过使用MS SQL服务器自定义的聚集函数完成当前的任务。

（实际上，可以让MS SQL服务器使用纯SQL语句实现结果可以确定的字符串连接聚集，不过，你需要多次使用RECURSIVE查询功能来实现。虽然可以把它当作一次有趣的学术性练习，但是这会生成运行速度慢、无法阅读且无法维护的代码，因此它不是一个可以使用在现实世界的解决方案。）

1.7Unicode 支持

ASCII一统江湖的日子已经一去不复返了，“字符（character）”和“字节（byte）”是可互换的以及“外国（foreign）”（从Anglocentric观点来看）文本是具有异国情调的异常。合适的国际语言支持不再是可选项。

所有问题的解决方案是Unicode。外面有许多对Unicode的误解。它不是一个字符集，不是一个代码页，不是一个文件格式，并且它和加密也没有任何关系。探索Unicode是如何工作的很让人着迷的事，但是这个超出了本文的范围，我打心底里推荐你Google它并且试试几个例子。

Unicode对应的重要的数据库功能如下：

Unicode编码文本（对于我们的目的而言，这意味着只能使用UTF-8或者UTF-16）是一个可变长度的编码。在UTF-8中，一个字符可以占1、2、3或4个字节。在UTF-16中，它可以占2或4个字节。这意味着进行获取子字符串、计算字符串长度等操作时，需要知道它们是Unicode编码的，这样操作才能正常进行。
并非所有字节序列都是合法的Unicode。操作合法的Unicode编码的文本而不知道它是Unicode编码，就像是生成一些不合法的Unicode文本。
UTF-8和UTF-16互不兼容。如果你每一个文件使用一种类型，然后把它们合并起来，你（可能）会得到一个既不是UTF-8格式也不是UTF-16格式的文件。
对于大部分适合ASCII的文本而言，UTF-8格式的空间效率是UTF-16格式的2倍。

PostgresSQL支持 UTF-8 。默认情况下，它的 CHAR 、 VARCHAR 、 TEXT 类型采用 UTF-8 格式，这意味着这些类型仅接受 UTF-8 数据和适用于 UTF-8 格式的所有转换，从字符串级联到使用正则表达式搜索，这些都是显示的 UTF-8 格式。上面这些都是有效的。

MS SQL Server 2008 不支持 UTF-16；但它支持 UCS-2 -- UTF-16 的一个过时的子集(a deprecated subset)。多数情况下，不会有问题. 但是, 偶尔会损坏数据。因为，它把文本当成宽字符(如 2 个字节)处理，所以遇到 UTF-16 的 4-字节字符时，会毫不犹豫地切掉一半。轻者，数据损坏。严重的时候，会导致你的工具链中某些东西出现问题，后果非常严重。那些替微软辩护的人会立刻反驳：这是不可能的。因为，在 Unicode 的基本多文平面(basic multilingual plane)外面，还包含其他东西。这种说法实在是荒谬。数据库就是用来存储，读取和修改数据而已。一个数据库，如果因为存入错误的数据而出问题，那不是跟下载错误的文件会导致路由器崩溃一样荒唐？

2012年后MS SQL Server版本都支持UTF-16，如果你确定你为数据库设置了兼容UTF-16的校验规则。这在2012年后的版本中是一个莫名其妙的功能。不过，迟到总比不到好。

1.8. 可以正常使用的数据类型

一个普遍误解的概念是所有的数据库有相同的数据类型 – INT、 CHAR、 DATE等等。不是这样的。 PostgresSQL的数据类型系统非常有用、直观、没有 bug或效率低下的烦恼，显然数据类型的设计使用生产效率为核心。

相比之下，MS SQL服务器的类型识别系统就像是Beta版的测试软件。它不具有PostgreSQL类型识别系统所具有的功能集，而且由于诸多陷阱使得用户异常困惑，从而让粗心的用户掉入缺陷网中。我们仔细对比一下：

CHAR、VARCHAR和其他字符类型

PostgreSQL：它的文档积极鼓励你只使用TEXT类型。它是一个高性能的、UTF-8编码的文本存储类型，可存储的字符串高达1GB。它还支持PostgreSQL具有的所有文本操作：简单地字符串连接和字串提取；正则搜索、正则匹配和分割；全文搜索；强制类型转换；字符转换等等。如果你有文本数据，那么就可以定义为TEXT字段，并存储。再者，由于TEXT字段(或者是CHAR或者VARCHAR字段)的内容必须采用UTF-8编码，因此就不存在编码兼容的问题。由于UTF-8是事实上的通用的文本编码。因此把文本转换成此种编码就非常容易和可靠。再者因为UTF-8是ASCII编码的超集，所以此种转换通常非常简单或者说不需要转换。正因为这样，它才运行的非常好。
MS SQL服务器：这是一次非常痛苦的经历。TEXT和NTEXT两个类型都有，而且可存储的大小达2GB。然而，令人沮丧的是它们都不支持类型转换。另外，微软建议不要使用它们 -在将来的MS SQL服务器版本里，这两个类型都将被删除。你应当取代性地使用CHAR、VARCHAR以及它们的N前缀版本。很不幸的是：VARCHAR(MAX)具有很差的性能，而且VARCHAR(8000)(即紧邻的最大存储量，由于某些原因)最大只能存储8000字节。（NVARCHAR最大只能存储4000字符。）想一下PostgreSQL怎么样在一个每个数据库上使用单一的文本编码，从而让一切平稳地运行的？ MS却没有做到这样：

就像早期版本的SQL服务器那样，在编码转换期间发生数据丢失是不会汇报的。[ 链接]

换句话说，MS SQL服务器可能会损坏数据，而且只有遇到其他非数据错误时你才知道。很简单，这就是个麻烦制造者。可对数据进行沉默性更改、损坏或者丢失的数据分析平台就是一个巨大的缺陷。想想不使用服务器而使用昂贵的ECC RAM做为防止因宇宙射线而造成的数据损坏荒谬行为，然后在其上运行软件，那么无论如何都可能造成数据损坏。

日期和时间类型

PostgreSQL:你可以使用DATE、TIME、TIMESTAMP和TIMESTAMP WITH TIME ZONE类型，所有这些类型都会做到你期望那样。它们还具有极为出色的范围和精度，支持从公元前５世纪到未来30万年的毫秒级精度。它们也接受多种格式的输入，最后一个是它们还提供对时区的完整支持。它们还可以与UNIX时间互转，这一点在与其他系统的互操作方面就非常重要。它们还可以取特殊值infinity和-infinity。这不是一个形而上的、神学的、哲学方面的语句，而是一个非常有用的语义结构。例如，设置用户密码的过期日期为infinity，表示他们不需要修改密码。处理这种事情的标准方式是使用NULL或者遥远的未来的某个日期，不过，他们是一群愚蠢的黑客－他们不但把不精确的信息存储到数据库，而且还需要编写应用逻辑来弥补。当开发人员看到NULL或者3499-12-31的时候，会怎么样呢？如果你幸运，那么他会认识到要进行密钥握手，而不会因此产生任何混乱。如果不幸运，那么他会假设这个日期是未知的，或者这个日期确实指的是第四个千年，那么你就会遇到问题。像这样的黑客、工作场景和凑合的程序设计的累积结果就会生成一个不可靠的系统、不幸福的程序开发人员，从而增加商务上的风险。像infinity和-infinity这样非常有用的语义结构就允许你说出你所想，然后写出一致的、可读性好的应用逻辑。它们还支持INTERVAL类型，它非常有用，在这一节之后有专门的一节介绍。日期和时间类型的强制转换为其他类型和它们之间的转换非常简单和直观－你可以强制转换任意一日期和时间类型为TEXT，to_char和to_timestamp函数给你提供最大的灵活性，可以让你使用格式字符串实现两个方向的互转。例如：
```
SELECT to_char('2001-02-03'::DATE, 'FMDay DD Mon YYYY');--这条语句会生成字符串"Saturday 03 Feb 2001"
```
另一方向的转换如下：
```
SELECT to_timestamp('Saturday 03 Feb 2001', 'FMDay DD Mon YYYY'); --这条语句会生成时间戳2001-02-03 00:00:00+00
```
像往常一样，这一切都运行的非常好！作为一名数据分析师，我非常关心数据库对日期的处理能力，因为日期和时间是以许多不同的格式来表现的，而且对分析来说它们通常都非常重要。
MS SQL服务器: 日期类型只有由正的四位数字组成的年份，因此日期局限在公元0001到公元9999之间。它们也不支持infinity和-infinity。它们也不支持interval类型，因此日期的算法乏味笨拙。你可以在它们与UNIX时间间互相转换，不过处理的技巧是在UNIX新纪元1970-01-01T00:00:00Z上增加秒数，因此你需要知道UNIX新纪元，而且要把它硬编码到应用里。日期转换在这儿特别值得一提，是因为即便遵循MS SQL服务器粗略的标准，日期转换仍然让人感觉到吐血般的恐怖。使用CONVERT函数替代了PostgreSQL的to_char和to_timestamp，不过它运行方式如下：

SELECT CONVERT(datetime, '2001-02-03T12:34:56.789', 126); --这条语句会生成datetime值: 2001-02-03 12:34:56:789

就是这样-你只要知道“126”是把字符串转换为某种格式的datetime的编码即可。MSDN给出与这些奇奇怪怪数字对应的表。我没有给出与PostgreSQL相同的例子，这是因为我没有找到与格式"Saturday 03 Feb 200"相对应的奇怪的数字。如果某人给出的数据含有这个日期类型，那么我猜你将不得不做一些字符串处理的工作了（很遗憾，在MS SQL服务器里几乎没有字符串处理功能。）

INTERVAL

PostgreSQL:INTERVAL类型表示一个时间段，如“30微秒”或者“50年”。它也可以是负数，这看起来有些不合常理，如果你知道一个单词“以前（ago）”存在的话就不会了。PostgreSQL也知道“ago”，事实上它也接受类似‘一天前（1 day ago）’字符串作为interval值（这个值在内部用-1天的时间段表示）。interva让你有一个直观的日期计算，并且作为第一类的数据类型来存储持续时间。它们像你期望的那样准确，并且可以自由的改造和转换成任何有意义的数据，也可以由任何有意义的数据转换而来。

MS SQL Server：不支持interval数据类型。

数组

PostgreSQL：以一等数据类型的方式支持数组，这就意味着数据表的字段、PL/PGSQL中的变量、函数的参数等等都可以是数组。数组可以包含你喜欢的任意数据类型，包括其他数组数据类型。这一点非常，非常有用。你可以使用数组完成以下事情：

存储调用具有任意数量返回值函数的所返回的结果，比如正则匹配函数；

把字符串表示为多个整型字表示的ID，可用在快速文本匹配算法里。

对属于不同分组的多个数据值进行聚集计算，对跨表计算尤其有用

在不需要使用昂贵的连接的情况下，对多个数据值执行行操作。

能够精确、确切地表示工具套件中其他应用中的数组数据。

给你工具套件中的其他应用传送数组数据。

Brainfuck

Malbolge

MS SQL服务器:不支持数组。

JSON
- PostgresSQL: 完全支持JSON，包括很多实现JSON类型和表字段类型相互转化的工具函数。解析（json对象转化为字符串）和反解析（字符串转化为json对象）通过简单的组件进行处理，这是PostgresSQL中一个智能的、健壮的规则。 JSON也可以在PL/V8过程化语言中以你期望的方式进行使用 – 实际上，一个JSON类型的内部状态在一个自定义的聚合（详见这里，它的转化函数使用PL/V8编写）中提供了一种非必要的/必要的两全齐美的效果，这个功能是如此强大、方便，感觉像是骗人的。JSON（以及它的变种，如JSONB）毫无疑问是web和其它一些数据平台（如MongoDB、 ElasticSearch，实际上包括使用RESTful风格接口的任意系统）上进行数据传输的标准格式。有理想的分析即服务供应商（或开发人员）可以留意下。
- MS SQL Server：不支持JSON。
</li> </ul>
- HSTORE

本文由用户 jopen 自行上传分享，仅供网友学习交流。所有权归原作者，若您的权利被侵害，请联系管理员。

转载本站原创文章，请注明出处，并保留原始链接、图片水印。

本站是一个以用户分享为主的开源技术平台，欢迎各类分享！

本文地址：https://www.open-open.com/lib/view/open1417568649964.html

数据库服务器 PostgreSQL

热门搜索

PostgreSQL vs. MS SQL Server

0.本文是关于什么的？

1. 为什么说 PostgreSQL 比 MS SQL Server 强的多

1.1. 支持 CSV

1.2.人机工程

你能够在Linux,BSD等平台上运行PostgreSQL（当然，在Windows上也可以）

1.4程序语言特性

1.5支持原生正则表达式

1.6自定义聚合函数

1.7Unicode 支持

1.8. 可以正常使用的数据类型

1.9. 脚本化

1.10.很好地整合了外部语言

1.11 文档

1.12.日志记录确实有用

1.13支持

1.14灵活的，可脚本化的数据库转储

1.15可靠性

1.16 易于安装和更新

1.17.构建软件捐赠模块

1.18. 自由与免费

2. 反驳

2.1. 不是说大牌厂商更可靠么!

2.2 不过，如果PostgreSQL的作者挂了怎么办？

2.3 但是开源软件不安全、不稳定、不值得信任、不适用于企业级生产等等！

2.4. 可是 MS SQL Server 能利用多个 CPU 内核处理单条查询!

2.5. 我只会 MS SQL Server , 没用过 PostgreSQL!

2.6. 难道数以亿计的 Microsoft 用户都错了?!

2.7. 真要这么好，怎么可能会免费!

2.8 但是你存在偏见！

2.9. 但是“PostgreSQL"是个愚蠢的名字！

2.10.但是SSMS比PGAdmin要好点！

2.11MS SQL Server可以直接从Excel导入！

2.12PostgreSQL比MS SQL Server要慢！

热门搜索

PostgreSQL vs. MS SQL Server

0.本文是关于什么的？

1. 为什么说 PostgreSQL 比 MS SQL Server 强的多

1.1. 支持 CSV

1.2.人机工程

你能够在Linux,BSD等平台上运行PostgreSQL（当然，在Windows上也可以）

1.4程序语言特性

1.5支持原生正则表达式

1.6自定义聚合函数

1.7Unicode 支持

1.8. 可以正常使用的数据类型

1.9. 脚本化

1.10.很好地整合了外部语言

1.11 文档

1.12.日志记录确实有用

1.13支持

1.14灵活的，可脚本化的数据库转储

1.15可靠性

1.16 易于安装和更新

1.17.构建软件捐赠模块

1.18. 自由与免费

2. 反驳

2.1. 不是说大牌厂商更可靠么!

2.2 不过，如果PostgreSQL的作者挂了怎么办？

2.3 但是开源软件不安全、不稳定、不值得信任、不适用于企业级生产等等！

2.4. 可是 MS SQL Server 能利用多个 CPU 内核处理单条查询!

2.5. 我只会 MS SQL Server , 没用过 PostgreSQL!

2.6. 难道数以亿计的 Microsoft 用户都错了?!

2.7. 真要这么好， 怎么可能会免费!

2.8 但是你存在偏见！

2.9. 但是“PostgreSQL"是个愚蠢的名字！

2.10.但是SSMS比PGAdmin要好点！

2.11MS SQL Server可以直接从Excel导入！

2.12PostgreSQL比MS SQL Server要慢！

2.7. 真要这么好，怎么可能会免费!