PostgreSQL ERROR: 42P01: relation “[Table]” does n

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.

Answer 2

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"
 ...

Answer 3

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