Java PreparedStatement is Undesirably Passing Lower-Case String Names to Case-sensitive PostgreSQL Database in SQL Query

Question

I am using a Java PreparedStatement object to insert rows into a Postgres database table. (my code is below:)

...
// define values to insert
LocalDateTime localDateTime = LocalDateTime.now();
int num = 3;            
double frac = 0.8;

//create PreparedStatement
PreparedStatement st = conn.prepareStatement("INSERT INTO TABLE_Final_Records(Datetime, Number_n, Fraction_f)" 
+ "VALUES (?,?,?)" );

// set placeholder ("?") values as Postgres types
st.setObject(1, localDateTime);
st.setInt(2, num);
st.setDouble(3, frac);

// execute statement
st.executeUpdate();
st.close(); 
...

However, when I execute the above code I get the error (via a toString() of a caught SQLException):

org.postgresql.util.PSQLException: ERROR: relation "table__final__records" does not exist.

Where each uppercase character specified in the code above appears as lower case in the text of the thrown exception.

If I manually rename the database table to be all lower case, the error is eliminated, without changing the code. However it is replaced with:

org.postgresql.util.PSQLException: ERROR: column "datetime" of relation "table_final_records" does not exist

An obvious work-around would be to manually convert all uppercase characters in all the table and column names in the Postgres database to lower case, however I would like to know how to force case-sensitivity on such statements so that PostgreSQL database table & column names with uppercase characters can be properly recognized and updated.

Thanks

Answer 1

You could work around the issue by quoting the identifiers in the query:

PreparedStatement st = conn.prepareStatement(
    "INSERT INTO \"TABLE_Final_Records\"(\"Datetime\", \"Number_n\", \"Fraction_f\")" 
    + " VALUES (?,?,?)" 
);

But bottom line: the issue that you are getting denotes that your database table and columns were created with double quotes surrounding the idenfiers. Generally, you want to avoid quoting the identifiers when you declare them, since this makes them case sensitive - which, as you are experiencing, makes writing query tedious and lengthy.