java - Simple query fails with: operator does not exist: bigint = character varying with Postgres jdbc - Stack Overflow

admin管理员组

文章数量:1123592

I have simple query:

/* crm_person */ 
        
            select /*query crm_person*/ 
              crm_pers.*
            from crm_person crm_pers
            where 1=1
         and crm_pers.id = ?

And params map:

As result I am getting error - org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = character varying Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 133

I have simple query:

/* crm_person */ 
        
            select /*query crm_person*/ 
              crm_pers.*
            from crm_person crm_pers
            where 1=1
         and crm_pers.id = ?

And params map:

As result I am getting error - org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = character varying Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 133

• java
• postgresql
• jdbc

Share Improve this question Follow asked 21 hours ago GintsGintsGintsGints 83511 gold badge77 silver badges1515 bronze badges 5

• crm_person tables id column is bigint. – GintsGints Commented 21 hours ago
• I think the issue is that your parameter is sent as a string (character varying). I don't know Java but you'd need to sent it as an integer or cast the column to a string crm_pers.id::varchar to make the types match. – AymDev Commented 21 hours ago
• Please show the code that sets the parameter and executes the statement. – Laurenz Albe Commented 21 hours ago
• ps.setObject(bindedParamsCount++, param); – GintsGints Commented 21 hours ago
• and yes... param is a string. Could be I have to find why other queries with same code do not fail :) – GintsGints Commented 21 hours ago

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

As the error states, the column is a (big) integer, which should be equivalent to Java's long. Instead of using setObject with a string, you can use setLong:

ps.setLong(1, Long.parseLong(param);

For me problem was groovy script, which set parameter id as string if not specified:

def params = utils.queries.params().put("id", personId).getMap()

so this helped:

def params = utils.queries.params().put("id", personId as Long).getMap()

本文标签：