COALESCE and ARRAY in postgresql - query

Question

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

GMB · Accepted Answer · 2020-10-22 12:44:20Z

2

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

answered Oct 22, 2020 at 12:44

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lubrum Over a year ago

With this small change -> AND ( ?::text[] is null or column = any(?::text[]) ) the query worked. I am using org.postgresql.Driver from Jdbc.

Gordon Linoff · Accepted Answer · 2020-10-22 12:43:28Z

0

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.

answered Oct 22, 2020 at 12:43

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

lubrum Over a year ago

This did not work for me because the column is a varchar, but I'd like to pass an array argument. Also, this gave me the error: ERROR: malformed array literal: "test" DETAIL: Array value must start with "{" or dimension information. SQL state: 22P02

Collectives™ on Stack Overflow

COALESCE and ARRAY in postgresql - query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related