-1

I'm using DBeaver to write script for my PostgreSQL database.

I have a PostgreSQL DB with Tables autogenerated by C#/EFCore (Microsoft ORM) - I receive SQL Error [42P01] if I don't add double quotes around table names when I cut and paste my ORM queries to DBeaver. I got [42703] for fields without double quotes. I do not have to add double quotes in C# code but it appears to be required in DBeaver?

example:

  • select * from Dnp3PropertyBase => SQL Error [42P01]

  • select * from "Dnp3PropertyBase" => OK, all results shown...

Does anybody know if I can change a parameter in DBeaver somewhere in order to enter table names and fields without double quotes?

Note: Using DBeaver 22.3.2 (latest on 2023-01-11)

Update After reading: Postgresql tables exists, but getting "relation does not exist" when querying

show search_path => public, public, "$user"

SELECT * FROM information_schema.tables => All tables are in public schema

SELECT * FROM information_schema.columns => All columns are in public schema

Question: How to be able to cut and paste my EFCore generated queries from Visual Studio output window to DBeaver query without having any errors regarding table names and field names?

Eric Ouellet
  • 10,996
  • 11
  • 84
  • 119
  • 3
    SQL Error [42P01] indicated the table is not found. Unless table names (and column names) are double quoted, the letters are automatically converted to lowercase. As a result they are not found. – Bjarni Ragnarsson Jan 11 '23 at 20:30
  • How to prevent automatic lower casing ? – Eric Ouellet Jan 11 '23 at 20:34
  • Do you know if the lower casing happens in DBEaver or PostgreSQL and if we can prevent it? – Eric Ouellet Jan 11 '23 at 20:44
  • 3
    Would recommend snake casing the table and column names instead, the error is there for a reason you should not name tables like this. – Nathan Erkamp Jan 11 '23 at 20:48
  • I understand your point. I'm not sure it is the best solution because I use EF Core (ORM from Microsoft) and it is the ORM which generate table names and field names which I expect uses proper standard. I will try to find another solution. Thanks a lot for your help. I didn't know snake casing. It is also possible that I will do your solution if I don't find anything easier to me. – Eric Ouellet Jan 11 '23 at 21:01
  • 2
    "*if I can change a parameter somewhere in order to enter table names and fields without double quotes*" - no, there is no such parameter. "*Do you know if the lower casing happens in DBEaver or PostgreSQL and if we can prevent it*" - _you_ (or some tool) created the tables and columns with mixed case using double quotes. It's required by the SQL standard to make then case sensitive in that case, and thus you need the double quotes to refer to them. If you don't want that, then don't create the tables using the double quotes. –  Jan 11 '23 at 21:18

1 Answers1

0

First let me copy @a_horse_with_no_name comment:

Unquoted names are folded to lower case in Postgres (and to uppercase in Oracle, DB2, Firebird, and many others). So SomeTable is in fact stored as sometable (or SOMETABLE). However quoted identifiers have to preserve the case and are case sensitive then. So "SomeTable" is stored as SomeTable

Many peoples recommended me to go with snake case which I didn't want to go with initialy because all tables were auto generated by EF Core (Microsoft C# ORM). I told myself that Microsoft would do standard things. Microsoft use the exact "class" name in code as the table name , by default. That appears to me very logical in order to stay coherent and apply the same rules everywhere. C# recommended to use Camel case for classes so each table names end by default in Camel case instead of snake case.

PostgreSQL seems to promote users to use snake casing because they lower case every non double quoted names. According to a_horse_with_no_name, and I think the same, only PostgreSQL has the behavior of lower casing down every table names and field names which are not double quoted in SQL script. That behavior (changing casing for non double quoted names) appears to me as being very limitative. It also has hidden effect that could be hard to find for non initiated peoples coming from other DB world.

According to PostgreSQL doc, they recommend to use nuget package (.UseSnakeCaseNamingConvention()). It probably works fine for TPH (table per hierarchy) which is recommended by Microsoft for performance. But it does not works for table name for TPC (table per class) because of actual bugs in EFCore 7 (see Github project).

I received that message at the end of "update-database":

Both 'WindTurbine' and 'ResourceGenerator' are mapped to the table 'resource_generator'. All the entity types in a non-TPH hierarchy (one that doesn't have a discriminator) must be mapped to different tables. See https://go.microsoft.com/fwlink/?linkid=2130430 for more information.

PostgreSQL doc : TPH supported OK but not for table in TPC (2023-01-12). I use TPC then I had to force each table name directly through TableAttribute.

My solution For table name, I use snake casing by manually add a "Table" attribute to each of my classes with the proper name like this sample:

[Table("water_turbine")]
public class WaterTurbine : ResourceGenerator

For fields, I use the EFCore.NamingConventions NugetPackage which works fine for fields names. Don't forget that if you have 2 classes mapped to the same object, it is because you are using TPC and did not force table name through TableAttribute.

This way all my table and fields names are snake casing and I can cut and paste any query dumped in my debugger directly in any SQL script window of DBeaver (or any SQL tool).

Eric Ouellet
  • 10,996
  • 11
  • 84
  • 119
  • 1
    @a_horse_with_no_name Typo: the standard requires that identifiers be folded to **upper case**. – Laurenz Albe Jan 12 '23 at 07:09
  • 1
    "*I think it is only PostgreSQL that has the behavior of lower casing down every table names and field names which are not double quoted in SQL script*" - well, the SQL standard requires unquoted names to be folded to upper case which pretty much every database does that (except for SQL Server which is always(?) case preserving and MySQL where it depends on the configuration and the file system). Postgres doesn't comply with the standard by folding to lower case instead, but the problem is the same SomeTable is a different name than "SomeTable" as required by the SQL standard. –  Jan 12 '23 at 07:22
  • @a_horse_with_no_name, Thank a lot for all the precision, that's definitively very interresting to know. I dont' understand what you mean by " but the problem is the same SomeTable is a different name than "SomeTable" as required by the SQL standard". Do you mean that it is lower cased by PostgreSQL? One think sure, I personnelly thing that everything should not be lower or upper cased in any way because it is a hidden behavior (side effects). But that is only my opinion. – Eric Ouellet Jan 12 '23 at 18:34
  • 2
    Unquoted names are folded to lower case in Postgres (and to uppercase in Oracle, DB2, Firebird, and many others). So `SomeTable` is in fact stored as `sometable` (or `SOMETABLE`). However quoted identifiers have to preserve the case and are case sensitive then. So `"SomeTable"` is stored as `SomeTable` - in all DBMS that comply with the SQL standard. –  Jan 12 '23 at 18:36
  • @a_horse_with_no_name, OK Thanks to confirms, I already got it but I wasn't sure. Thanks' for the enumeration of databases, it will surely help me in the future. – Eric Ouellet Jan 12 '23 at 18:39