Search code examples
sqlpostgresqlcoalesce

PostgreSQL COALESCE with IN

I have the following example statement with PostgreSQL:

SELECT ((? IS NULL) OR (1 IN ?))

Where ? can either be null or something like (1, 2, 3). It obviously works fine for non-null, but causes a syntax-error for null values.

I've been trying around with COALESCE but without any luck or Google help so far.

How can I write the above statement properly to handle ? = NULL ?

Solution

  • You can use Postgres array instead of a list, e.g.:

    SELECT 
    coalesce(1 = any(null), true) a,
    coalesce(1 = any(array[1,2,3]), true) b,
    coalesce(1 = any('{1,2,3}'), true) c;

 a | b | c 
---+---+---
 t | t | t
(1 row)