Re: Possible bug related to primary keys autogeneration

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.