Title: PreparedStatement.setString on int/numeric columns in PostgreSQL JDBC
Tags: java;postgresql;jdbc
Question: I am working with some legacy code that performs database operations in a generic way, so that the User/developer can work with a different database by changing only the JDBC driver.
I have a problem with PostgreSQL JDBC driver. My test case:
//ddl
CREATE TABLE test
id numeric,
name text,
//java code
String sqlCmd = "INSERT INTO test values (?, ?)";
PreparedStatement ps = connection.prepareStatement( sqlCmd );
ps.setString( 1, "1" );
ps.setString( 1, "name1" );
ps.executeUpdate();
With Postgres, the result of this case is an exception with message: "can't cast string to int..."
Is it inappropriate to use PreparedStatement.setString() to set values that database expects to be numeric?
Should I expect the JDBC driver to automatically convert java types to database types?
This test passes with other databases, including H2 and MySQL. Does the failure with PostgreSQL reflect a bug in the JDBC driver? Is it possible to make this case work without changing code?
Comment: This just reflects different database vendor priorities. mysql is permissive to a fault. a large part of H2's reason for existence is as a local database for applications that use another db in prod, so it wants to allow anything any other popular database allows. postgres is actually trying to do the correct thing.

Comment: Using `setString()` to be "database agnostic" was stupid to begin with. Every driver **must** implement the approriate `setXXX()` methods for each datatype that is defined in the JDBC API. Using the wrong method simply makes the application actually *more* database dependent relying on the driver and the DBMS to silently convert one type to another. _If_ a generic method has to be used (instead of the correct one). `setObject()` would have been a *much* better choice.

Here is the accepted answer: The documentation for java.sql.PreparedStatement has this to say:
Note: The setter methods (setShort, setString, and so on) for setting IN parameter values must specify types that are compatible with the defined SQL type of the input parameter. For instance, if the IN parameter has SQL type INTEGER, then the method setInt should be used.
Whether a particular database or JDBC driver allows you to be sloppy about that is its own affair, but you are not justified in expecting that all drivers will allow such slop, even if certain ones do.
Comment for this answer: I have never seen any JDBC driver that failed to send a numeric value to the DBMS via setString(). So I was surprised when that happend with Postgres JDBC driver. But documentation about PreparedStatement will be good argument to make changes in legacy code.

Here is another answer: You are using setString() method to insert integers and postgres could not do that,  Use
ps.setInt(1, INTEGER_VALUE);
Comment for this answer: Please explain downvoting..!
Comment for this answer: @ostry knows this solution, he is asking for a reason why this is happening. He expecting workaround that does not change existing legacy code.

Here is another answer: while migrating from oracle database to postgresql, I found that may help you to use setString with numeric types and date types as well.
Just you have to use the connection parameter stringtype=specified as mentioned in the documentation.
https://jdbc.postgresql.org/documentation/head/connect.html

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 2, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:11/05/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<java><postgresql><jdbc>', 'Title': 'PreparedStatement.setString on int/numeric columns in PostgreSQL JDBC', 'ViewCount': 9171, 'id': 26767947, 'user_AboutMe': None, 'user_AccountId': 2869276.0, 'user_DisplayName': 'ostry', 'user_Reputation': 107.0, 'user_Views': 23.0, 'user_id': 2462956.0}}