I'd like to use an array as a filter in the query, but also, I need to return all values if no values were provided (by preparedStatement - java).
Something like this:
AND column = any(coalesce(?, column_example::text[]))
The problem is that column_example is a varchar column. How to achieve this need with sql (postgres) ?
Version:
PostgreSQL 11.9 (Ubuntu 11.9-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit
I need to return all values if no values were provided
You could just do:
AND ( ? is null or column = any(?::text[]) )
The casting of the parameter to an array might not be necessary, depending on whether your driver is able to properly pass such datatype. If so:
AND ( ? is null or column = any(?) )
Hmmm . . . how about two separate comparisons?
AND
(column = ? OR ? IS NULL AND column = any(column_example::text[]))
You need to pass the parameter in twice or use a named parameter.
