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

Question

There is a table in postgres DB test1 having schema :

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?

Community · Accepted Answer · 2017-05-23 11:46:38Z

8

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()"

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Apr 27, 2016 at 9:37

Ann

3131 gold badge4 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

d1ll1nger · Accepted Answer · 2016-04-27 08:33:21Z

1

Yeah, drop the double quotes here:

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

Becomes

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

Alternatively do the casting as you mentioned.

answered Apr 27, 2016 at 8:33

d1ll1nger

1,70113 silver badges16 bronze badges

2 Comments

Ann Over a year ago

Thanks for your comment . Yes, this would work . But the problem is there are some thousand queries to be modified and params are sent from a User Interface and we are trying to port an existing application to postgres.We found another solution for it. Posted it below.

Diego Lope Loyola Over a year ago

In 2021, a numeric column, accepts insert values like "50.5" or 50.5

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related