postgres insert string to numeric column - auto-typecast does not happen

3
votes

There is a table in postgres DB test1 having schema :enter image description here

We are using spring frameworks jdbcTemplate to insert data as below: 

Object[] params = {"978","tour"};

jdbcTemplate.update("insert into test1 values (?,?)", params);

But this gives the exception :

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into test1 values (?,?)]; nested exception is org.postgresql.util.PSQLException: ERROR: column "id" is of type integer but expression is of type character varying ERROR: column "id" is of type integer but expression is of type character varying

This works for Oracle database through implicit type conversion, but postgres does nOt seem to work that way.

Could this be an issue with postgres driver?

A workaround would be to cast explicitly:

insert into test1 values (?::numeric ,?)

But is there better way to do the conversion as this does not seem like a good solution since there are lot of queries to be modified and also there can be other such casting issues too.

Is there some parameter that can be set at DB level to perform an auto cast?

2
databasepostgresqlcastingjdbctemplate

2 Answers

7
votes

We found the answer here

Storing json, jsonb, hstore, xml, enum, ipaddr, etc fails with "column "x" is of type json but expression is of type character varying"

A new connection propertyshould be added :

String url = "jdbc:postgresql://localhost/test";

Properties props = new Properties();

props.setProperty("user","fred");

props.setProperty("password","secret");

props.setProperty("stringtype", "unspecified");

Connection conn = DriverManager.getConnection(url, props);

https://jdbc.postgresql.org/documentation/94/connect.html

"If stringtype is set to unspecified, parameters will be sent to the server as untyped values, and the server will attempt to infer an appropriate type. This is useful if you have an existing application that uses setString() to set parameters that are actually some other type, such as integers, and you are unable to change the application to use an appropriate method such as setInt()"

1
votes

Yeah, drop the double quotes here:

Object[] params = {"978","tour"};

Becomes

Object[] params = {978,"tour"};

Alternatively do the casting as you mentioned.