CODEWITHSUNDEEP
sqlarrayspostgresqlwhere-clausesql-null
lubrum
lubrum

Reputation: 444

COALESCE and ARRAY in postgresql - query

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

Upvotes: 1

Views: 3434

Answers (2)

GMB
GMB

Reputation: 222682

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(?) )

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271121

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.

Upvotes: 0

Related Questions