PostgreSQL vs. MS SQL Server

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。

• HSTORE

• PostgreSQL:HSTORE是PostgreSQL的一种扩展，它用一种数据类型实现了对键值对的快速存储。与数组相似，这一种数据类型非常有用，因为几乎每一种编程语言都有这一设计理念（也可以这么说，因为这一设计理念非常有用，所以几乎每一种编程语言都采纳它）。JavaScrip中的对象，PHP的关联数组，Python中的字典，C++中的有序映射(std::map)和无序映射(std::unordered_map)，Go中的map等等。键值对存储这一设计思想如此重要和有用，以致于把它做为一种NoSQL数据库主要的存储模型。好吧，我们就称这样的存储模型为键值存储。这一数据类型还有我们意想不到的一些非常有趣的用法。近期，有位同事问我是否有一种好的方法能把文本数组中重复的项删除。我采用以下语句解决这个问题：

SELECT akeys(hstore(my_array, my_array)) FROM my_table;

即把数组放入到HSTORE的键值对里，这样就会强制删除重复的项（因为不允许键重复），然后再从HSTORE中提取键就可以了。这再一次体现了PostgreSQL的功能多样性。

• MS SQL服务器：不支持键值对存储。

• 范围类型

• PostgreSQL:范围类型能够很好的体现范围这一概念。每一个数据库开发人员都层遇到过start_date和end_date这样的字段，而且他们中的大多数人还不得不编写逻辑代码来检测是否出现重叠的现象。一些人甚至会发现在范围上采用BETWEEN进行连接这一处理方法由于很多原因而深陷错误的泥沼之中。PostgreSQL的处理方法是把时间范围定义为一级数据类型。你不仅仅可以把时间（INT或者NUMERIC或者其他类型）范围放入单独的数据值中，而且还可以使用大量的内置操作符来安全快速地对范围进行维护和查询。你甚至还可以使用为范围特别开发的索引，这样就可以大大地提高使用操作符进行查询的速度了。简言之，PostgreSQL非常重视范围，而且还提供了高效处理范围的各种工具。我不想把这篇文章写成一系列PostgreSQL文档的链接，但是我还想提一下，我建议你亲自去阅读一下（，如果预定义的类型不能满足你的要求，那么你可以自己定义自己需要的类型。你不需要更改源代码，PostgreSQL数据库提供了许多方法做这些事情）。
  

• MS SQL服务器:不支持范围类型。

• NUMERIC和DECIMAL

• PostgreSQL:NUMERIC(以及DECIMAL-它们两个同义）几乎可以达到任意精度：在小数点之前它可以支持131，072位十进制数，在小数点后可支持16,383位十进制数。如果你正在管理一家银行，进行技术性计算，在彗星上实现飞船着陆或者做一些无法容忍四舍五入所带来的误差，那么你就可以使用这种类型。
  

• MS SQL服务器：NUMERIC(以及DECIMAL-它们两个同义）总体上支持的精度是38个十进制有效位。

• XML(很惭愧，XML已经有些过时了...)

• PostgreSQL:支持XML数据类型，PostgreSQL数据库有大量的函数进行XML处理。支持Xpath查询

• MS SQL服务器：终于，见到好的消息了！MS SQL服务器也支持XML数据类型，同时提供大量对XML处理的方法。

1.9. 脚本化

　　PostgreSQL 完全可以由命令来操作. 因为它在操作系统中就是这么用的 (Windows 操作系统是个例外). 这种方式既高效, 又安全. 如果有需要, 你甚至可以在手机上, 通过 SSH 登录服务器配置 PostgreSQL(这种事, 本人干过几次).  通过脚本, 能执行诸如: 自动部署, 性能调节, 安全, 管理 和分析任务等操作.  跟图形用户界面不一样, 脚本能被复制, 版本控制, 文档化, 自动化, 审查化(Reviewed), 批量化, 以及差异化(Diffed). 对于重要的工作来说, 文本编辑器和命令行才是王道.

　　MS SQL Server 通过图形用户界面(GUI)操作.  就算加上 Powershell 的辅助, 我也不知道它能在多大程度上实现自动化; 如果你在 Google 上搜 MS SQL Server 的用法, 得到的答案会基本上是 "在数据库上点右键, 然后选 任务...(Tasks...)". 在速度慢或者高延时的网络连接中, 使用文本命令行的效果, 远比用图形用户界面好的多.  在写这篇文章的时候,  我正准备通过 VPN,  登录一台位于 3,500 英里外的服务器, 执行管理操作. 这里的 WiFi, 信号相当不稳定. 谢天谢地, 我用的是 Ubuntu/PostgreSQL.

　　(真的有地球人通过图形用户界面管理服务器吗?)

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

　　由编程环境连接并使用PostgreSQL非常、非常简单，这是因为libpq,即PostgreSQL的外部应用程序接口(API)，设计的非常完美而且文档也非常完备。这就意味着编写嵌入到PostgreSQL里的应用将非常简单方便，这也使得PostgreSQL功能更多，更适合于应用在数据分析上。我曾多次用C或者C++编写了简短的程序，它可进行PostgreSQL连接，获取数据以及对获取到的数据进行大量计算，其中用到了多线程或者特殊的CPU指令-不过不适合对数据库本身进行数据填入。我还写过这样的C程序：它使用了setuid，从而允许在PostgreSQL里以普通用户执行某些管理员才能完成的任务。快速而且简练地做到这些真的很方便。

　　MS SQL服务器整合的外部语言的方法各有不同。有时候你需要安装其他驱动。有时候你需要创建类来存储你所查询的数据，这种情况下就意味着你在编译时就要知道数据是什么样子。最为重要的是，它的文档让人困惑、一团糟，要做成一件事就要 

　　耗费不必要的时间，而且非常痛苦。