3

Say I have a customer table and would like to make a query with raw SQL. The below code does not work:

List<Customer> customers = _db.Customer.FromSql("SELECT * FROM Customer").ToList();

It fails with the error code

'42P01: relation "customer" does not exist'

Thorkil Værge
  • 2,727
  • 5
  • 32
  • 48

1 Answers1

3

In certain situation Postgres will produce case sensitive table names, so you may have to reference the table names this way. This is done by adding quotes around the table names like so: "Customer".

You may need to include the schema as well. This should work:

List<Customer> customers = _db.Customer.FromSql("SELECT * FROM \"public\".\"Customer\"").ToList();

Assuming your schema name is "public". Otherwise insert your schema name instead.

Thorkil Værge
  • 2,727
  • 5
  • 32
  • 48
  • 1
    Postgres doesn't "produce" case sensitive names - it's all a result of how you wrote your CREATE TABLE statement. The rules are [quite clear](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS) but the use of quoted identifiers is [strongly discouraged](https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_upper_case_table_or_column_names) –  Mar 08 '21 at 13:27
  • You are right of course. I just ended up in the situation with quoted identifiers since I used the SQL Manager wizard to create the database. – Thorkil Værge Mar 08 '21 at 13:53