PostgreSQL为什么将所有内容默认为小写？ [英] Why does PostgreSQL default everything to lower case?

问题描述

在尝试使用MySQL（5.1）相当多之后，我试图熟悉Postgres（9.2），因为我被少数 MySQL的陷阱。但是，在使用Postgres的前五分钟里，我遇到了其陷阱之一，我敢肯定，这肯定会打击每个人：

I'm trying to familiarize myself with Postgres (9.2) after a fair bit of MySQL (5.1) usage, since I've been bitten by a handful of MySQL's gotchas. However, in my first five minutes with Postgres I ran into one of its gotchas, which I'm sure hits everyone:

• 默认情况下，PostgreSQL会将未引用的所有内容转换为小写。

对我来说这没什么大不了的，因为有两个明显的解决方法：

This isn't too big of a deal to me, since there are a couple of obvious workarounds:

• 将所有内容都用引号引起来。


• 允许所有内容以小写形式命名。

但是我想知道为什么。考虑到我认为这个设计决策会引起多少争论，我很惊讶我在互联网上找不到任何理由。关于为何采用这种方式设计Postgres，是否有人有透彻的解释，或者最好是指向某些开发者宣言的链接？我很感兴趣。

But I'm wondering why. Considering how much contention I imagine this design decision causes, I'm surprised that I couldn't find any rationale on the internet. Does anybody have a thorough explanation, or preferably a link to some developer manifesto, as to why Postgres was designed this way? I'm interested.

推荐答案

SQL标准指定将未加引号的标识符折叠为大写。其他许多RDBMS都遵循这种标准。 Firebird和Oracle都这样做。这意味着标识符匹配在默认情况下不区分大小写。当涉及基本查询的兼容性时，此行为非常重要。在这方面，MySQL的行为确实是一个异常值。

The SQL standard specifies folding unquoted identifiers to upper case. Many other RDBMS's follow the standard in this way. Firebird and Oracle both do. This means that identifier matching is, by default, case insensitive. This behavior is very important when it comes to compatibility in basic queries. In this regard MySQL's behavior is a real outlier.

但是PostgreSQL通过折叠为小写字母而偏离了标准。有很多一般的原因使之被认为更具可读性，等等，因为您可以使用case来提示语法。

However PostgreSQL deviates from the standard by folding to lower case. There are general reasons why this is considered more readable, etc. because you can use case for cuing syntax. Something like:

SELECT foo FROM bar WHERE baz = 1;

当案件折成更低的价格时，这会更自然。相反的另一种折叠方式是：

This is more natural when cases are folded to lower. The alternative folding opposite would be:

select FOO from BAR where BAZ = 1;

大体上与前一种行为（折叠为小写）类似，因为它在折叠时会更好地强调sql操作在另一种情况下，则不强调操作并强调标识符。鉴于许多查询的复杂性，我认为前者的效果更好。

In general like the former behavior (folding to lower case) becasue it emphasizes the sql operations better while folding to the other case de-emphasizes the operations and emphasizes the identifiers. Given the complexity of many queries, I think the former works better.

总的来说，我在postgres邮件列表上看到的最多讨论是，每个人都同意标准-强制性行为已被破坏。所以以上就是我对问题的理解。

In general the most discussion I have seen on the postgres mailing lists have been that everyone agrees the standard-mandated behavior is broken. so the above is my understanding of the issues.

这篇关于PostgreSQL为什么将所有内容默认为小写？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！