Re: Possible bug related to primary keys autogeneration

Alright, I can understand the rationale around the behavior (and indeed
"This isn't likely to be popular" ;-)).
I'll go with a workaround then.

Thanks for the very quick feedback!

On 15/07/2016 16:49, Dave Cramer wrote:
> as the comments around
>
> https://github.com/pgjdbc/pgjdbc/blob/7311b4ef160231780323573911c1543d0515340b/pgjdbc/src/main/java/org/postgresql/jdbc/PgStatement.java#L1205
>
> suggest if escape is turned on we will quote the returning. The reason
> for this is to allow people who really want ID returned as opposed to id.
>
> this is unlikely to be changed.
>
> Dave Cramer
>
> davec(at)postgresintl(dot)com <mailto:davec(at)postgresintl(dot)com>
> www.postgresintl.com <http://www.postgresintl.com>
>
> On 15 July 2016 at 10:41, Andrea Bergia <a(dot)bergia(at)list-group(dot)com
> <mailto:a(dot)bergia(at)list-group(dot)com>> wrote:
>
> Being consistent with cases is generally good advice, but
> unfortunately my code has to run on multiple databases and it's
> very hard to control cases in all the code.
>
> Besides, it seems to me that the behavior is inconsistent. I can
> use whatever case I want in the INSERT INTO clause:
>
> INSERT INTO Documents (name) VALUES ('abc');
> INSERT INTO Documents (NAME) VALUES ('abc');
> INSERT INTO Documents (NaMe) VALUES ('abc');
>
> but when using the "returning" clause, I have to be careful about
> my identifiers... Besides, if the driver wasn't quoting the
> RETURNING clause, everything would work well (as I have tested
> using the debugger). So, any chance that can be changed in the
> driver?
>
>
> On 15/07/2016 16:30, Dave Cramer wrote:
>> This is an artifact of how postgresql treats case.
>>
>> PostgreSQL folds to lower case. short version "do not use UPPER
>> CASE identifiers"
>>
>> If you are intent on using upper case you have to use new
>> String[]{"\"ID\""}) to force upper case (to be honest I'm not
>> even sure that would work)
>>
>> as I said don't use UPPER CASE identifiers
>>
>> Dave Cramer
>>
>> davec(at)postgresintl(dot)com <mailto:davec(at)postgresintl(dot)com>
>> www.postgresintl.com <http://www.postgresintl.com>
>>
>> On 15 July 2016 at 10:09, Andrea Bergia <a(dot)bergia(at)list-group(dot)com
>> <mailto:a(dot)bergia(at)list-group(dot)com>> wrote:
>>
>> Hello, I have an issue regarding the retrieval of
>> autogenerated keys using JDBC.
>>
>> I am running both the client and the server on windows; the
>> client using the JDBC driver 9.4.1208 and the server has
>> version "PostgreSQL 9.5.3 on x86_64-pc-mingw64, compiled by
>> gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit".
>>
>> I have the following schema:
>>
>> CREATE TABLE Documents (
>> id SERIAL,
>> name VARCHAR(100),
>> CONSTRAINT PK_Documents PRIMARY KEY (id)
>> );
>>
>> and the following Java code:
>>
>> try (Connection connection =
>> DriverManager.getConnection("jdbc:postgresql://localhost/sampledb",
>> "sampledb", "")) {
>> connection.setAutoCommit(false);
>> try (PreparedStatement ps =
>> connection.prepareStatement("INSERT INTO Documents (name)
>> VALUES (?)", new String[]{"ID"})) {
>> ps.setString(1, "DocName");
>> ps.executeUpdate();
>>
>> try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
>> if (!generatedKeys.next()) {
>> throw new RuntimeException("Should have been
>> able to retrieve the generated keys");
>> }
>> int generatedKey = generatedKeys.getInt(1);
>> System.out.println("The generated key is " +
>> generatedKey);
>> }
>> }
>> }
>>
>> I get the following exception:
>> Exception in thread "main" org.postgresql.util.PSQLException:
>> ERROR: column "ID" does not exist
>>
>> Debugging a bit, the issue seems to be in PgConnection.java,
>> line 1641: since "escape" is set to "true", the driver is
>> adding RETURNING "ID", quoting the column name. Since the
>> column name's case is different between the CREATE TABLE
>> statement and the code, PostgreSQL generates an exception. In
>> fact, if I use new String[]{"id"}, the program works.
>>
>> I'm wondering whether this can be classified as a bug, or is
>> intentional. The driver doesn't quote column names generally;
>> it seems to me that this behavior is a bit annoying. However,
>> I have no idea what the implications of removing the escaping
>> would be. I do have a workaround for the moment, but I would
>> like to know whether this is something that can be changed or
>> not.
>>
>> I have uploaded a complete, minimal project which includes
>> the code above at
>> https://github.com/andreabergia/psql-jdbc-possible-bug-report.
>>
>> Thanks for your time, and for your excellent product.
>>
>> --
>> Andrea Bergia
>> List S.p.A.
>>
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org
>> <mailto:pgsql-jdbc(at)postgresql(dot)org>)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>>
>
> --
> Andrea Bergia
> List S.p.A.
>
>

--
Andrea Bergia
List S.p.A.