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

Question

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.

Answer 1

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.