tmro

I am no DBA guy. No doubt about that. I would be even more frustrated if I were one. Today I stumbled upon another PostgreSQL issue but this time around I decided to share my anger with you.

Problem: Imagine that you need to write a query that returns the values that match a condition or null if nothing is matched. In other words you are trying to do a coalesce. So your query would be something like Select * from EMPLOYEE where :empNoParameter IS NULL or :empNoParameter = EMP_NO. Normally you’d rewrite this as: Select * from EMPLOYEE where EMP_NO = COALESCE(:empNoParameter, EMP_NO) because COALESCE will return the first non-null value that it finds … Now the problem is that in EJBQL or the new Java Persistence Query Language there is no Coalesce function. Damn. So you have to write in the first style. The problem is that, in PostgreSQL (8.3), you will get a org.postgresql.util.PSQLException: ERROR: operator does not exist: character
varying = bytea or something similar to that.

Solution: The query was Select * from EMPLOYEE where :empNoParameter IS NULL or :empNoParameter = EMP_NO and it failed; that happened because when the PostgreSQL JDBC driver first encountered the empNoParameter named parameter it had no clue what type it was because empNoParameter IS NULL does not really reveal the type. So the driver just assigned some default type to the param. Which would be okay if you didn’t need to do some other comparison later on with the same parameter. So when you come across :empNoParameter the second time around it will have the type it was given initially and will only work as intended if the EMP_NO is indeed of that type. Which in most cases will not be. So here is the solution: swap the tests around: Select * from EMPLOYEE where :empNoParameter = EMP_NO OR :empNoParameter IS NULL. Now the named query will work because the JDBC driver will be able to identify :empNoParameter’s type from the first test it has to make.

Rant: Why don’t you do a look ahead you silly PostgreSQL driver? Eh? Why don’t you?

Note: This is my guess as I have not studied the JDBC driver code. I know it worked in more than one scenario though so hopefully it will work for you too…

Cheers…