Postgresql:文字表名 [英] Postgresql: literal table names

问题描述

我正在制作一个需要构建 Postgresql 查询的应用程序，该查询将在表名是保留关键字等情况下成功执行.

在 Sql Server 语法中，这是通过将所有内容包裹在方括号 [] 中实现的，即 SELECT * FROM [database].[schema].[table_name].>

我认为 Postgresql 中的等价物是使用双引号 "" 即 SELECT * FROM "database"."schema"."table_name".

但是，当我在 Postgresql 中尝试此操作时出现错误

<块引用>

关系 X 不存在

这有效:

SELECT * FROM "postgres"."schema_a".Academic_Attainment

但这没有:

SELECT * FROM "postgres"."schema_a"."Academic_Attainment";

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

有什么建议吗?

解决方案

如手册中所述 未加引号的标识符折叠为小写.

带引号的标识符也区分大小写，因此 Foo" 与 foo" 是不同的名称.

所以名称Academic_Attainment 与academic_attainment 相同.如果你真的坚持使用那些可怕的双引号，那么你需要使用小写标识符:

SELECT *FROM schema_a".academic_attainment"

一般来说，强烈建议不要 完全使用带引号的标识符.经验法则:永远不要使用双引号，你就没事了.

---

如果您正在构建动态 SQL，请使用 format() 函数与 %I 占位符一起执行此操作.如有必要(并且仅在那时)，它将处理引用，例如

format('select * from %I.%I', 'public', 'some_table') 产生 select * from public.some_table 但 format('select * from %I.%I', 'public', 'group') 产生 select * from public.group"

---

与您的问题无关:Postgres 不支持跨数据库查询，因此您不应该养成将数据库名称包含在完全限定名称中的习惯.您使用的语法仅在您连接到数据库 postgres 时才有效.所以我建议停止在任何表引用中使用数据库名称.

I am making an application that needs to construct Postgresql queries that will execute successfully in scenarios when table names are reserved keywords etc.

In Sql Server syntax this is achieved by wrapping everything in square brackets [] i.e. SELECT * FROM [database].[schema].[table_name].

I thought the equivalent in Postgresql was the use of double quotes "" i.e. SELECT * FROM "database"."schema"."table_name".

However, when I try this in Postgresql I get the error

Relation X doesn't exist

This works:

SELECT * FROM "postgres"."schema_a".Academic_Attainment

But this doesn't:

SELECT * FROM "postgres"."schema_a"."Academic_Attainment"

Related to: Escaping keyword-like column names in Postgres

Any suggestions?

解决方案

As documented in the manual unquoted identifiers are folded to lowercase.

A quoted identifier is also case sensitive, so "Foo" is a different name than "foo".

So the name Academic_Attainment is the same as academic_attainment. If you really insist on using those dreaded double quotes, then you need to use a lower case identifier:

SELECT * 
FROM "schema_a"."academic_attainment"

In general it's strongly recommended to not use quoted identifiers at all. As a rule of thumb: never use double quotes and you are fine.

---

If you are constructing dynamic SQL, use the format() function to do that together with the %I placeholder. It will take care of quoting if necessary (and only then), e.g.

format('select * from %I.%I', 'public', 'some_table') yields select * from public.some_table but format('select * from %I.%I', 'public', 'group') yields select * from public."group"

---

Unrelated to your question: Postgres doesn't support cross-database queries, so you should not get into the habit including the database name into your fully qualified names. The syntax you are using only works because you are connected to the database postgres. So I would recommend to stop using the database name in any table reference.

这篇关于Postgresql:文字表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！