Re: Error while returning auto-generated values

On Tue, 13 Nov 2012, Amila De Silva wrote:

> The detailed description of the problem is like this;
> We have used uppercase for our ddl queries (without doublequotes). According
> to the explanation, these are downcasted, and column names should be created
> in lower case (which is correct of course).
>
> In the same way our insert queries should be down casted, since quotes
> aren't used at all. But the problem arises when defining return columns.. In
> the way it have behaved, the return column (which is the SUBSCRIBER_ID )
> should also be downcasted since there hasn't been any double quote used to
> preserve the case. But the problem is in return queries, case is preserved.
>

The problem is that the quoting and downcasing rules are clear for SQL
identifiers in SQL strings, but they are not the same as the JDBC API.
For the JDBC API, there are many calls that take table and column names
that do not have the same rules. For example, DatabaseMetaData.getTables.

http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getTables%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String[]%29

getTables' Javadoc says, "tableNamePattern - a table name pattern; must
match the table name as it is stored in the database" Which implies that
it must be provided to getTables downcased to match the downcasing
Postgres did to an unquoted identifier.

I understand what you want to happen, but it is not simple in the general
case. Consider the following table:

kjurka=# create table trouble (subscriber_id int, "SUBSCRIBER_ID"
int, """SUBSCRIBER_ID""" int);
CREATE TABLE
kjurka=# \d trouble
Table "public.trouble"
Column | Type | Modifiers
-----------------+---------+-----------
subscriber_id | integer |
SUBSCRIBER_ID | integer |
"SUBSCRIBER_ID" | integer |

Now you've got a real problem and you have to have a clear rule for what
values must be provided to the JDBC API to get which columns returned.
What the postgresql JDBC driver does is match the getTables API, you must
provide a value to the API matching that stored in the database.

Kris Jurka