在Postgres中转义类似关键字的列名

Question

如果Postgres表中的列名为year，则设置该列值的 INSERT 查询应如何编写？

例如：INSERT INTO table (id, name, year) VALUES ( ... );在单词 year 附近出现错误。

- shybovycha

Answer 1

简单地将year用双引号括起来，就可以避免它被解释为关键字：

INSERT INTO table (id, name, "year") VALUES ( ... );

从文档中获取到：

有第二种类型的标识符：转义标识符或者叫做带引号的标识符。它是由将一系列的任意字符用双引号（"）括起来形成的。一个带引号的标识符始终是一个标识符，而不是一个关键字。所以 "select" 可以用于引用一个名为 "select" 的列或表，而未被引号括起来的 select 则会被视为关键字，因此在期望使用表或列名称的地方使用时，会导致解析错误。

- NPE

---

注意：在没有双引号的情况下，PostgreSQL会将所有标识符转换为小写。MyTable、myTable和mytable都是相同的。而双引号可以避免这种转换。因此，"MyTable"不再与mytable相同。 - A.H.

更好的做法是避免使用保留词或混合大小写作为标识符，这样就不必使用双引号或遇到奇怪的错误消息。 - Erwin Brandstetter

@djjek: https://dev59.com/-nI-5IYBdhLWcg3wKE6x - NPE

问题在于当你在一个已经建立的项目上工作时。 - ceruleus

@HoàngLong，是的，它可以。update "user" set "password" = 'value...'; 可以很好地工作。 - Phill

Answer 2

如果在任何字段/列中不提供引号，Postgres默认会将其小写。并且当涉及到列名时，Postgres将跳过关键字检查。

在您的情况下，我认为在处理列时添加引号并非强制性要求。但是，如果您使用Postgres注册的关键字作为Table、Schema、Function或Trigger的名称，那么您必须使用双引号，或者可以用点连接指定模式名称。

假设order是由Postgres注册的关键字。在某些情况下，您必须将此关键字用作表名。

此时，Postgres将允许您创建具有keywords的表。这就是Postgres的优美之处。

要访问order表，您必须使用双引号或在表名之前使用模式名称。

例如：

select * from schema_name.order;

2.

select * from "order";

同样地，您可以使用这种组合。希望这能对您有所帮助。

- Mayur

Answer 3

为了保险起见：始终引用标识符！为此，您必须使用定界标识符构建插入语句。

SQL 2003指定定界标识符应使用双引号"引用，如果标识符中出现双引号，则必须重复双引号。请参见BNF：

https://ronsavage.github.io/SQL/sql-2003-2.bnf.html#delimited%20identifier

这是引用标识符的Java代码：

static String delimited_identifier (String identifier)
{
  return "\"" + identifier.replaceAll ("\"", "\"\"") + "\"";
}

这是构建插入的代码：

static String build_insert (String table, String[] columns)
{
  StringBuilder sql = new StringBuilder ();
  StringBuilder values = new StringBuilder ();

  sql.append ("INSERT INTO ");
  sql.append (delimited_identifier (table));
  sql.append (" (");
  int c = 0;
  if (columns.length > 0) {
    sql.append (delimited_identifier (columns[c]));
    values.append ("?");
  }
  for (++c; c < columns.length; c++) {
    sql.append (", ");
    sql.append (delimited_identifier (columns[c]));
    values.append (", ?");
  }
  sql.append (") VALUES (");
  sql.append (values.toString ());
  sql.append (")");

  return sql.toString ();
}

例子：

String sql = build_insert ("Person", new String[]{"First name", "Last name"});

- ceving

---

@questionto42 我不确定我是否理解。您想让我将评论移到答案中吗？ - ceving

我认为添加一些实用代码（无论使用什么编程语言）来帮助避免与问题相关的问题实际上非常有用。 - undefined

然而，我对在Postgres模式中引用所有标识符（例如创建表/创建视图脚本）并不确定...我觉得这是进入痛苦世界的一种方式...我看到的是，如果你使用双引号，那么你只能使用双引号（并且大小写要完全一样）来访问那些模式对象。这可能非常痛苦，例如你有来自第三方的代码生成SQL并且没有正确处理这个问题。我建议在Postgres中：1. 避免自定义标识符与关键字冲突；2. 在自己的数据访问代码中不要使用双引号。 - undefined

更好的是-避免使用PostGres - undefined

@MemeDeveloper 你无法避免关键词冲突，因为你没有权力拒绝新的关键词。每当Postgesql决定向语言中添加新的关键词时，如果你不引用自己的标识符，你的应用程序可能会崩溃。不引用标识符就是自杀行为。这只是一个愚蠢的想法，但却是一种受欢迎的愚蠢。 - undefined