PostgreSQL ERROR: 42P01: the relation "[Table]" does not exist

Question

I had this strange problem using PostgreSQL 9.3 with tables created using qoutes. For example, if I create a table using qoutes:

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

the table is correctly created, and I see that the quotes are saved when viewed in the pgAdminIII SQL pane. But when I query the DB to find a list of all available tables (using the following query), I see that the result does not contain quotation marks 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 using quotation marks, I cannot use the table name returned from the above query immediately, because it is incorrect and generates an error published in the header.

I could try to surround the table names with quotes in all queries, but I'm not sure if it will work all the time. I am looking for a way to get a list of table names that are in quotation marks as a result.

I have the same problem with column names, but I hope that if I can find a solution to the problem with table names, a similar solution will work for column names.

Answer 1

you have two options: - no quotes: then everything will automatically have lowercase and case insensitive - with quotes: now everything is case sensitive.

I would highly recommend NOT using quotation marks and making PostgreSQL behave case-insensitively. it makes life a lot easier. once you get the quote, you should use it EVERYTHING, since PostgreSQL will start to be very accurate.

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 cheating at all costs. stay with 7 bit ascii for object names.

Answer 2

The string function used to correctly quote identifiers in the SQL statement quote_ident() , which references a good example (used in conjunction with the associated quote_literal() ).

To use your example and mix in other results:

 select quote_ident(table_schema) as table_schema, quote_ident(table_name) as table_name ... table_schema | table_name --------------+------------------ ... public | good_name public | "table" public | some_table public | "something else" public | "Tom work" public | "TEST" ...