1

I would like to have a native query for Postgresql, however I could not make it work with dates. I can receive date as null, or date as date, so I like to work in both cases:

" case when coalesce (?3, null) = ?3 then true " +
" else p.start_date = ?3 end"

if ?3 is the third parameter and can be null and it is of type LocalDate

I tried many variations, but could not make it work for both cases, separately it will work:

  1. if coalesce (?3, null) = ?3 for date param null it will work
  2. if p.start_date = ?3 for date param != null it will work

The column in the database is date

Thanks:)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
vikifor
  • 3,426
  • 4
  • 45
  • 75
  • Did you try `p.start_date = coalesce(?3, p.start_date)` (instead of the `case` expression) –  Oct 02 '20 at 08:30
  • Just try it I received: – vikifor Oct 02 '20 at 08:38
  • org.postgresql.util.PSQLException: ERROR: COALESCE types bytea and date cannot be matched Position: 266 – vikifor Oct 02 '20 at 08:38
  • Then whatever you use to run that statement sends the value as the wrong type. This will most certainly work with plain JDBC. –  Oct 02 '20 at 08:40

1 Answers1

0

This worked for me :

 case when ?3 <> '' then p.start_date = (cast(?3 as date))
      else 1 = 1 end

So, I am passing string parameter and convert it to date, because nothing else worked for me

vikifor
  • 3,426
  • 4
  • 45
  • 75