0

I have created a PostgreSQL database 12.7 and converted it to an esri geodatbases, then I copied the tables and feature classes from a file geodatabases , this caused the al column names to be lowercases however we are already integrating with an API that is case sensitive and needs the column names as per the design example IDs are upper case and fields are camle case.

is there any PostgreSQL setting to disable this auto to lower column names? because when we quoted the column names the gis system could not find the columns with quoted names.

MMALSELEK
  • 777
  • 6
  • 13
  • 1
    You need to use [quoted identifiers](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS) but I strongly recommend to **not** do that. –  Aug 05 '22 at 15:30
  • I Checked that already but as I mentioned this is a geodatabase as well and gis failed to read the quoted column names, is there a way to disable it from the RDBMS ? – MMALSELEK Aug 05 '22 at 15:33
  • You have to build your own version of PostgreSQL. This is not a configuration item, it's hardcoded in the source code of the project so you have to change the source code first. – Frank Heikens Aug 05 '22 at 15:37
  • 1
    Did the table schema get copied from another database? The SQL standard would be to fold identifiers to all upper case if not quoted(Postgres deviates by folding to lower case), so I am not sure how you preserved CamelCase without quoting in your previous usage? Seems to me you need to look at whether the export/import process can quote identifiers. You will need to explain this "the gis system could not find the columns with quoted names" further. **Add answers as update to question**. – Adrian Klaver Aug 05 '22 at 15:46
  • Is this really a problem? When you go to query your database or insert data, PostgreSQL doesn't really care about case: schema names, table names, column names, stored procedures, and views can be any mixture of upper/lower case. Maybe if the API creates temporary tables with uppercase characters, then you could get into trouble? – bfris Aug 05 '22 at 17:07
  • @AdrianKlaver when postgresql database is used as gis database it is called a Geodatabase it will have extra layer of spatial database engine that manage the data import, export and versioning. So the GIS system does not accept quoted column names. It throw errors: attribute not found error – MMALSELEK Aug 05 '22 at 17:27
  • @bfris the API is generic and reads what ever fields exist in any table and we want to avoid extra customization and configuration – MMALSELEK Aug 05 '22 at 17:31
  • 1) You need to add information to your question not the comments. 2) `Geodatabase` seems to be `ArcGIS` term. I have seem them usually referred to as spatial databases. 3) You have not identified the GIS application you are using over the database 4) You need to show the code that fails and the complete error message. **Again add information as update to question**. – Adrian Klaver Aug 05 '22 at 17:33
  • Could you share some examples of the DDL for the CREATE TABLE statements and an example of the SELECT statements your application is doing? PostgreSQL treats everything as lower case, CREATE TABLE fOo turns into foo. SELECT * FROM FoO will also select from foo. When nothing is using quote identifiers, you wouldn't have a problem. When everything is using quote identifiers AND is using the same case for the names, you wouldn't have a problem either. So what is going on? – Frank Heikens Aug 05 '22 at 17:56

1 Answers1

1

The only way to prevent PostgreSQL from folding into lowercase is to use double quoted identifiers. This is already sort of answered here. It also includes a link to the manual on identifiers.

progowl
  • 41
  • 9