java sql error column is of type integer but expression is of type character varying

Question

I have prepared sql statement:

PreparedStatement insert_query = db.prepareStatement(String.format(INSERT_QUERY, table, 
                              columns.toString(), placeholder));
System.out.println(insert_query);

Result:

INSERT INTO "article" (article_text, article_id, article_title) VALUES (?, ?, ?) RETURNING article_id

Next I create values list, set parameters and execure insert query:

ArrayList<String> values = new ArrayList<String>(); -- > is [New CONTENT, 4, New TITLE]
for (int i = 1; i <= values.size(); i++) {
            insert_query.setString(i, values.get(i-1));
        }
System.out.println(insert_query);
insert_query.executeUpdate();

I got an error:

INSERT INTO "article" (article_text, article_id, article_title) VALUES ('New CONTENT', '4', 'New TITLE') RETURNING article_id

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "article_id" is of type integer but expression is of type character varying

Something is wrong with article_id. I put it as string here. However when I manually try this query in PostgreSQL everything works(((

JFPicard · Accepted Answer · 2015-03-26 13:34:00Z

4

For the article_id, you must do insert_query.setInteger instead of insert_query.setString. When I check your exception, the article_id is sent to the DB as a String. That's why you have this exception.

answered Mar 26, 2015 at 13:34

JFPicard

5,1973 gold badges22 silver badges47 bronze badges

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

Comments

ovod · Accepted Answer · 2015-03-26 13:52:15Z

2

Better solution I found is initiate values list as:

ArrayList<Object> values = new ArrayList<Object>();

And insert into statement object:

insert_query.setObject(i, values.get(i-1));

answered Mar 26, 2015 at 13:52

ovod

1,1784 gold badges20 silver badges36 bronze badges

Collectives™ on Stack Overflow

java sql error column is of type integer but expression is of type character varying

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related