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.

Sepulchre

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.

Expanded

Agree that not using quotes in PostgreSQL makes life easier but not always possible in my case since my application doesn't create the databases. It should be able to read data from third party PostgreSQL databases and I can't control how tables are created by others. In any case, what's surprising is that PostgreSQL doesn't tell me that a certain table was created with quotes but when I use an unquoted table name in a query, it doesn't allow it. – Sepulchre

it knows automatically that quotes were used as soon as there is one non-lowercase letter there. so if a table is listed as Test or so it already means that you got to be case sensitive. – Infinite

I thought of that as a last option since implementing a check to see if at least one character is in upper case before adding quotes would be an overkill if PostgreSQL itself can tell me whether a table contains quotes or not. – Sepulchre

Answer 2

While using npg package as your data store ORM you are expecting the ORM framework (Entity Framework in our case) to generate the sql statement you might face a PostgreSQL exception the relation 'Table Name' does not exist

Either the table is not created or the generated SQL statement is missing something. Try to debug using visual studio you will see that the schema name is not leading the table name

SELECT "ID", "Name", "CreatedBy", "CreatedDate" 
FROM "TestTable"; 

while PostgreSQL is expecting the schema name. Resolution is in the DBContext class override the OnModelCreating method and add modelBuilder.HasDefaultSchema("SchemaName"); and execute the base constructor which should look like the following

protected override void OnModelCreating(ModelBuilder modelBuilder)   {             
  modelBuilder.HasDefaultSchema("PartyDataManager");                  
  base.OnModelCreating(modelBuilder);         
}

Elinoreeliot

Answer 3

in my case. table in database has to be lowercase...change name dataTable do datatable help

Tracy

Please add more details to your answer. Have a look at stackoverflow.com/help/how-to-answer for more details. – Kruger

Answer 4

Keep the table name to lower case and it will solve the issue.

Penrose

Answer 5

A string function used to suitably quote identifiers in an SQL statement string is quote_ident(), which references a good example (used in conjunction with related 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's work"
 public       | "TEST"
 ...

Vladamar