Platform to Postgres - Case Sensitivity

#1

Sharing a question I encountered regarding case-sensitivity.

Question:
Let’s say I am trying to fetch data from an external table created on Postgres in my local machine. The table is created with the name “Building” in PG and schema name of the entity type is the same. However, while trying to fetch data from the type, it throws an error like “ERROR: relation “building” does not exist↵ Position: 37”

When I rename the table in PG to “building”, this works and pulls data from the external source successfully. Is this (automatic conversion to lowercase) expected behavior?

Response:
It depends on how the table was defined.

If I create the table with double quotes around the name (eg. “Building” vs “building”), PG will treat it as a case sensitive name. When the Platform builds queries for PG, it doesn’t use quotes so PG will look for the table with lower case regardless of whether building or Building is specified. In the case of the question above, it looks like “Building” was defined with double quotes, thus running into the relation error as the query is looking for the table with lower case, but the table we are trying to pull is case-sensitive.

Thus, assuming one has control over the PG tables, it is recommended to not use double quotes when specifying table or column names.

Also feel free to add any insight and refinement of this response!

1 Like