Which version of Postgres are you using, and on what platform? I’m not sure it will make any difference, but I might be able to install a copy of the same version of Postgres on my Windows server and have a play to see if I can break it.
This is mostly just some background but maybe it might flag something…
Most (if not all) relational databases are actually case-sensitive behind the scenes, but masquerade as case-insensitive. To achieve this apparent contradiction they are case-sensitive when identifiers are supplied in double-quotes, but pseudo-case-insensitive when identifiers are not quoted. The way they handle those two positions is to interpret unquoted-identifiers as meaning either all uppercase or all lowercase (and the upper or lower choice varies between vendors). For example Oracle’s default position is that table and column names are upper case. So it doesn’t matter whether I type lower or upper case identifiers, they get converted to uppercase and then compared with only upper-case names in the schema. If I enter identifiers in double-quotes they will be passed through as-typed and compared only with the names exactly matching the case of my identifiers.
As I understand it, Postgres is lower case by default, so an *un-*quoted identifier will always be treated as lower case no matter the case in which it is typed. So it makes sense that the default position is that it should be comparing identifiers in lower-case but what doesn’t make sense is that it should be doing that if it has identifier delimiters set to double-quotes in the Connector’s advanced settings, unless somewhere something is doing some kind of “too-early” conversion to the lower case default.
That is rather odd. Given the Postgres default, it is somewhat unfortunate if your database table names and column names have been created in upper case. I can only assume the table creation scripts had double-quotes round the identifiers. If they hadn’t been, then at least they would be in the (postgres) default lowercase and you probably wouldn’t be seeing this issue. As it is, you are rather being forced to work “against the grain” (even outside of Knime), as it means that any sql query you type will have to contain double-quotes around every identifier.
Just as in Oracle I would never dream of creating lower case table names for that very reason, in Postgres, I would imagine it would make life more difficult to have upper case names in the database.
In some databases (and I just looked at a MySQL installation), a switch at the database level turns off any kind of case-sensitivity, but it does that by totally preventing the creation of identifiers in anything but the default case, to avoid this issue.
This of course doesn’t help much with how to resolve your current issue, for which we will hopefully still find a resolution, but perhaps it might give some background about the possible case sensitivity issue.
Picking up on @bruno29a’s very good suggestion of attempting a query using the DB Query node, what this does mean is that for the query to work, and for it to respect the case of what you type, I would think that you will have to double-quote your table and column names within the query.