在PostgreSQL标识符中的下划线或camelCase，当编程语言使用camelCase？ [英] Underscores or camelCase in PostgreSQL identifiers, when the programming language uses camelCase?

问题描述

这一直困扰我一段时间，我无法得到一个感觉正确的解决方案 ...

给定一种OO语言，其中对象属性的通常命名约定是camelCased，并且示例对象如下所示：

  {
 id：667，
 firstName：Vladimir，
 lastName：Horowitz，
 canPlayPiano：true 
} 
  pre> 
 
 
如何在PostgreSQL表中建模此结构？
 
 
 
有三个主要选项： p> 
 
 
 
 
 unquoted camelCase列名
 
 
引用camelCase列名
 
 
未标注（小写）的名称带有下划线
 
 
它们各有其缺点：
 
 
 
未引号的标识符会自动折叠成小写。这意味着您可以创建一个具有 canPlayPiano 列的表，但混合大小写永远不会到达数据库。当您检查表格时，列将始终显示为 canplaypiano   - 在psql，pgadmin中，解释结果，错误消息，一切。
 
 
 
 
引用的标识符保留他们的情况，但一旦你创建它们，你将永远必须引用它们。 IOW，如果您创建一个具有canPlayPiano列的表，则 SELECT canPlayPiano ... 将失败。这对所有的SQL语句增加了不必要的噪音。
 
 
 
 
带下划线的小写名称是明确的，但是它们不能很好地映射到应用程序的名称语言正在使用。您必须记住使用不同的名称进行存储（ can_play_piano ）和代码（ canPlayPiano ）。它还可以防止某些类型的代码自动化，其中属性和DB列需要命名相同。
 
 
 
所以我在岩石和硬地之间被抓住（和一块大石头，有三个选择）。无论我做什么，有一部分会感到尴尬。在过去的10年左右，我一直在使用选项3，但我一直希望会有更好的解决方案。
 
 
 
我很感谢任何建议可能有。
 
 
 
 PS：我确实意识到案例折叠和引用需求来自于SQL标准，或者PostgreSQL适应标准。我知道它是如何工作的对于关于PG处理标识符的解释，我更感兴趣的是关于最佳实践的建议。
解决方案
鉴于PostgreSQL使用不区分大小写标识符带有下划线，应该更改应用程序中的所有标识符以执行相同操作显然不是那么为什么你觉得相反的是一个合理的选择？
 
 
 
 PostgreSQL中的惯例是通过标准合规性和用户的长期体验结合起来的。坚持使用它。
 
 
 
如果在列名和标识符之间进行翻译很繁琐，请让计算机做到这一点 - 他们擅长这样的事情。我猜测几乎所有的900万数据库抽象库都可以做到这一点。如果您有动态语言，则会将所有两行代码替换为CamelCase中的列名称到标识符。
 
This has been bothering me for a while, and I can't arrive at a solution that feels right...

Given an OO language in which the usual naming convention for object properties is camelCased, and an example object like this:
{
    id: 667,
    firstName: "Vladimir",
    lastName: "Horowitz",
    canPlayPiano: true
}
How should I model this structure in a PostgreSQL table?

There are three main options:

unquoted camelCase column names
quoted camelCase column names
unquoted (lowercase) names with underscores
They each have their drawbacks:

Unquoted identifiers automatically fold to lowercase. This means that you can create a table with a canPlayPiano column, but the mixed case never reaches the database. When you inspect the table, the column will always show up as canplaypiano - in psql, pgadmin, explain results, error messages, everything.
Quoted identifiers keep their case, but once you create them like that, you will always have to quote them. IOW, if you create a table with a "canPlayPiano" column, a SELECT canPlayPiano ... will fail. This adds a lot of unnecessary noise to all SQL statements.
Lowercase names with underscores are unambiguous, but they don't map well to the names that the application language is using. You will have to remember to use different names for storage (can_play_piano) and for code (canPlayPiano). It also prevents certain types of code automation, where properties and DB columns need to be named the same.
So I'm caught between a rock and a hard place (and a large stone; there are three options). Whatever I do, some part is going to feel awkward. For the last 10 years or so, I've been using option 3, but I keep hoping there would be a better solution.

I'm grateful for any advice you might have.

PS: I do realize where the case folding and the need for quotes is coming from - the SQL standard, or rather PostgreSQL's adaptation of the standard. I know how it works; I'm more interested in advice about best practices than explanations about how PG handles identifiers.
解决方案
Given that PostgreSQL uses case-insensitive identifiers with underscores, should you change all your identifiers in your application to do the same? Clearly not. So why do you think the reverse is a reasonable choice?

The convention in PostgreSQL has come about through a mix of standards compliance and long-term experience of its users. Stick with it.

If translating between column-names and identifiers gets tedious, have the computer do it - they're good at things like that. I'm guessing almost all of the 9-million database abstraction libraries out there can do that. If you have a dynamic language it'll take you all of two lines of code to swap column-names to identifiers in CamelCase.

                        
这篇关于在PostgreSQL标识符中的下划线或camelCase，当编程语言使用camelCase？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！