Compare date if not null

yossale_4038 0
Viewed 676

I need to find all the record with create date > X. X is a sql.Timestamp and might be null, in which case I want to just return all the records. So I tried: (createdAfter is Timestamp)

SELECT *
FROM sample AS s
WHERE s.isActive
  AND (:createdAfter ISNULL OR s.insert_time > :createdAfter)

But all I'm getting is

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1

However, if I'll do the same query where I'm checking for an arbitrary int to be null:

SELECT * 
FROM trades 
WHERE (:sInt ISNULL OR trades.insert_time > :createdAfter )

Then it works. What's wrong?

postgresql java
2 Answers

There is no simple solution if you want to stick with native queries like that. The null value is converted to a bytea value. See for example this and this.

That value is quite hard to be casted or compared to a timestamp value.

The problem is not so much with the first comparison it would be handled by using coalesce, like:

COALESCE(:createdAfter) ISNULL

because there is no comparison beteween actual values and the data type does not matter. But the comparison

sometimestamp::timestamp > null::bytea (casts just to show the actual types so not working)

would need more logic behind maybe procedure & exception handling or so, not sure.

So if JPQL or CriteriaQueries are not possible for you have only bad options:

  • contruct the query by setting params by string concatenation or so (NOT! and not sure if realy works)
  • use PreparedStatement queries, more code & effort
  • also if using Hibernate, using session api like in this answer
user_64133770

You can try using the pg_typeof function, which returns a text string, and using a CASE statement to force which comparisons are made (otherwise there's no guarantee that postgres will short-circuit the OR in the correct order). You can then force the correct conversion by converting to text and then back to timestamp, which is inelegant but should be effective.

SELECT *
FROM sample AS s
WHERE s.isActive
  AND 
    CASE WHEN pg_typeof( :createdAfter ) = 'bytea' THEN TRUE
       WHEN s.insert_time > ( ( :createdAfter )::text)::timestamp THEN TRUE
       ELSE FALSE 
    END
user_107305990
Related