Re: Possible bug related to primary keys autogeneration - Mailing list pgsql-jdbc

I've noticed that everything works as expected if I use:

connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

Unfortunately, this code does not work with Oracle, which is why I started testing the other version (the one with the specified column names). After this discussion, I can't think of any solution other than maintaining two version of my piece of code: one for Oracle, and one PostgreSQL, as I don't see Oracle changing their driver either. :-) It's not a really big deal.

Thanks.

On Fri, Jul 15, 2016 at 10:41 AM, Andrea Bergia <a.bergia@list-group.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');

​If you were to write that "INSERT INTO Documents (name) VALUES ('abc') RETURNING ID" you wouldn't have a problem because the server is going to lowercase everything for you anyway.  But as soon as you ask Java to append the returning clause for you it doesn't choose to apply PostgreSQL's case normalization rules but instead assumes that the case you provided in the code is the case that you desire - and adds quotes to enforce that decision.  This is nice when you use quote-required identifiers since you don't have to go escaping them in Java.  Maybe a PostgreSQL JDBC implementation could do this but I don't see it happening in this driver.

I could see maybe having a driver config to toggle this behavior...the specifics would be important.

David J.

-- 
Andrea Bergia
List S.p.A.