6

I write a postgresql and pass parameter name1 from my java code with Spring + Hibernate.

SELECT * FROM Test
WHERE name = COALESCE(:name1, name2)

but i got an error

ERROR: COALESCE types bytea and character varying

The type of name1 is String in my java code.

I tried to solve the problem

COALESCE(convert_from(:name1,'UTF8'), name2)

It will pass JUNIT Test in Eclipse. However when I run it on Tomcat, I will get an error

Error:convert_from(character varying, unknown) not exist

How to solve it? Thanks.

LoveTW
  • 3,746
  • 12
  • 42
  • 52
  • 3
    I don't know much about Spring / Hibernate, but you need to *cast* both values to the same type. This has *not* a question of encoding. `SELECT * FROM Test WHERE name = COALESCE(cast(name1 AS text), cast(name2 AS text))` – Erwin Brandstetter Dec 24 '13 at 04:21
  • Thank you for your reply. Could I ask why `convert_from(character varying, unknown) not exist` happen? Since `COALESCE(convert_to(:name1,'UTF8'), name2)` works well on PgAdminIII. Thanks:) – LoveTW Dec 24 '13 at 04:31
  • AS you have mentioned "name1" is String, I believe "name2" will also be String. Are you using "Query" or "SQLQuery" hibernate interface for SQL execution? It should be SQLQuery. – ms03 Dec 24 '13 at 05:48
  • Also, for setting parameter you need to use `.setParameter("name1", name1);` and not `.setString()`. setParameter() allows NULL as parameter values whereas .setString() does not. – ms03 Dec 24 '13 at 11:38
  • Error message says 'convert_from' not found, and in pgadmin you use 'convert_to' – Roman-Stop RU aggression in UA Dec 24 '13 at 14:44
  • To Roman Konoval: I edit my post, thank you. – LoveTW Dec 25 '13 at 10:09

1 Answers1

13

I have solve my question.

SELECT * FROM Test
WHERE name = COALESCE(CAST(:name1 AS TEXT), name2)

Thank Erwin Brandstetter for giving me advise.

LoveTW
  • 3,746
  • 12
  • 42
  • 52