关于”PostgreSQL错误：关系[表]不存在“问题的原因和解决方案

最近使用libpqxx库连接PostgresSql数据库，查询表时，抛出异常，错误信息如下：

PostgreSQL错误：关系[表]不存在

(PostgreSQL ERROR: 42P01: relation "[Table]" does not exist)。

关于这个问题的资料很少，找了半天，找到了原因。

我要查询的表，是在pgAdmin中手动添加了，其对应的SQL语句是如下形式：

create table “TEST” (“Col1” bigint);

注意：表名字母全部大写，使用双引号包含。

当我查询表格内容时，查询语句形式如下：

select * from TEST;

然后就抛出文章开始提到的异常。

问题就处在双引号上，如果你创建表时带有双引号，那后面涉及到该表格的地方都需要给表名加双引号。

相关内容：

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);

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.

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.

some example:

   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.

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