Postgres type casting and parameter binding conflict

Question

I get this error:

org.postgresql.util.PSQLException: No value specified for parameter 1.

For this kind of query:

UPDATE user
 SET Email = 'fake.' || id::varchar || '@localhost'
 WHERE Email is not null and char_length(Email)>0 and removed=false and id in (:ids)

I run this query as native query like this:

        sessionFactory.getCurrentSession()
            .createSQLQuery(sql)
            .setParameterList("ids", ids)
            .executeUpdate()
        ;

Seems like id::varchar is confusing Hibernate or JDBC. Any way to escape this maybe? Pretty standard Postgres syntax for type casting. Don't like casting syntax and would prefer to use this type mapping.

user330315 · Accepted Answer · 2021-03-12 12:12:00Z

3

Use concat() which will automatically cast the ID:

UPDATE user
 SET Email = concat('fake.', id, '@localhost')
 WHERE Email is not null 
   and char_length(Email) > 0 
   and removed = false 
   and id in (:ids)

Alternatively use the standard CAST operator:

UPDATE user
 SET Email = 'fake.' || cast(id as text) || '@localhost'
 WHERE Email is not null 
   and char_length(Email) > 0 
   and removed = false 
   and id in (:ids)

answered Mar 12, 2021 at 12:12

user330315

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

1 Comment

Nux Over a year ago

Hm... Interesting that concat does casting automatically and lpad doesn't 😕. And I really prefer this: lpad(id::varchar, 8, '0'), over this: lpad(cast(id as varchar), 8, '0')

Collectives™ on Stack Overflow

Postgres type casting and parameter binding conflict

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related