1

In a H2 database, I'm trying to query an array column with ANY, something like that:

ALTER TABLE person ADD COLUMN tel_numbers VARCHAR ARRAY;
SELECT * FROM person p WHERE '+123456' = ANY(p.tel_numbers)

This throws an error 90054-210 invalid usage of aggregatte function

I also tryied adding additional parenthesis around p.tel_numbers as for subquery, seeing the doc http://h2database.com/html/functions-aggregate.html#any but it does not help. How to write it correctly for H2? Hopefully a solution that also works in Postgres...

PS: The query is generated through Hibernate Query Builder, but that should not change the problem nor the answer.

Florian H.
  • 143
  • 9

1 Answers1

2

Quantified comparison predicates in the SQL Standard have a subquery in their right part.

Quantified comparison predicates with an array instead of a subquery is a non-portable PostgreSQL-specific feature. H2 currently (version 2.1.212) supports only a small subset of that feature: quantified equality comparison with array passed as JDBC parameter (= ANY(?)).

(There is also an unrelated aggregate function with the same name.)

You can use H2-specific

SELECT * FROM person p WHERE ARRAY_CONTAINS(p.tel_numbers, '+123456')

but this query isn't compatible with PostgreSQL. It means you need to use different queries in PostgreSQL and H2.

Fully standard-compliant query is

SELECT * FROM person p WHERE '+123456' = ANY(SELECT * FROM UNNEST(p.tel_numbers));

It works in PostgreSQL, but, unfortunately, it doesn't work in H2 (at least in current 2.1.212 and older versions) due to known issue.

Evgenij Ryazanov
  • 6,960
  • 2
  • 10
  • 18
  • If a single query is needed for H2 and Postgres, it would be quite easy to write an `array_contains()` function in Postgres. –  May 03 '22 at 09:46
  • Ok for the 2 queries, but not ideal ;-). I can distinguish the database through Hibernate thanks to https://stackoverflow.com/questions/27181397/how-to-get-hibernate-dialect-during-runtime – Florian H. May 03 '22 at 14:19