PostgreSQL错误：42P01：关系“[表]不存在 [英] PostgreSQL ERROR: 42P01: relation "[Table]" does not exist

问题描述

我有这个奇怪的问题使用PostgreSQL 9.3与使用qoutes创建的表。例如，如果我使用qoutes创建表：

I'm having this strange problem using PostgreSQL 9.3 with tables that are created using qoutes. For instance, if I create a table using qoutes:

create table "TEST" ("Col1" bigint);

表已正确创建，我可以看到在SQL窗格的pgAdminIII。但是当我查询DB找到所有可用的表的列表（使用以下查询），我看到结果不包含表名称周围的引号。

the table is properly created and I can see that the quotes are preserved when view it in the SQL pane of pgAdminIII. But when I query the DB to find the list of all available tables (using the below query), I see that the result does not contain quotes around the table name.

select table_schema, table_name from information_schema.tables where not table_schema='pg_catalog' and not table_schema='information_schema';

由于表是用引号创建的，我不能使用从上面的查询返回的表名

Since the table was created with quotes, I can't use the table name returned from the above query directly since it is unquoted and throws the error in posted in the title.

我可以尝试在所有查询中用引号括起表名，但我不确定是否将始终工作。我正在寻找一种方法来获得在结果中引用引号的表名的列表。

I could try surrounding the table names with quotes in all queries but I'm not sure if it'll work all the time. I'm looking for a way to get the list of table names that are quoted with quotes in the result.

我对列名也有同样的问题但我希望如果我能找到一个解决方案的表名称问题，类似的解决方案将适用于列名称。

I'm having the same issue with column names as well but I'm hoping that if I can find a solution to the table names issue, a similar solution will work for column names as well.

推荐答案

你有两个选择：
- 没有引号：那么一切将自动为小写和不区分大小写
- 带引号：从现在起，一切都是区分大小写的。

you have two choices: - no quotes: then everything will automatically be lowercase and non-case-sensitive - with quotes: from now on everything is case sensitive.

我强烈建议不要使用引号，使PostgreSQL行为不区分大小写。它使生活那么容易。一旦你得到报价，你就得使用它，因为PostgreSQL将开始非常精确。

i would highly recommend to NOT use quotes and make PostgreSQL behave non case sensitive. it makes life so much easier. once you get into quoting you got to use it EVERYWHERE as PostgreSQL will start to be very precise.

一些例子：

   TEST = test       <-- non case sensitive
   "Test" <> Test    <-- first is precise, second one is turned to lower case
   "Test" = "Test"   <-- will work
   "test" = TEST     <-- should work; but you are just lucky.

真的尽量避免这种骗术。保持对象名称的7位ascii。

really try to avoid this kind of trickery at any cost. stay with 7 bit ascii for object names.

这篇关于PostgreSQL错误：42P01：关系“[表]不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！