in H2 database, how to use ANY(array)?

user_7251133 0
Viewed 194

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.

h2
1 Answers

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.

user_117319870
Related