Reverse "expression operator ANY (array expression)"

All we need is an easy explanation of the problem, so here it is.

Postgres conveniently allows :

SELECT * FROM foo WHERE bar ~ ANY (?)

Where ? is an array of any 1+ regex patterns for column "bar" to match.

How can I elegantly do the opposite – pick out rows where 1+ columns match one regex ?

SELECT * FROM foo WHERE ANY (bar, baz, qux) ~ ?

I pored over Row and Array Comparisons, and Functions and Operators, and more, but docs are light on examples.
Feels like I’ve got all the pieces, but can’t connect ’em.

"row_constructor operator row_constructor" seems closer to what I need, but ~ is not available :

Row constructor comparisons are allowed when the operator is =, <>, <, <=, > or >=.

Besides which, I’d have to somehow explode 1 bound regex pattern into a row on the right-hand side.
Postgres complains that the right-hand side must be an array, when I’ve tried.

It’s not clear to me whether Composite Type Comparisons would support "~" :

Composite type comparisons are allowed when the operator is =, <>, <, <=, > or >=, or has semantics similar to one of these.

Besides which, I’m even more lost as to how I’d construct multiple columns into a record on the left side, and 1 regex pattern into a record on the right of the operator "~".

This is an approximation of what I want in behaviour :

SELECT * FROM foo WHERE CONCAT(bar::text, baz::text, qux::text) ~ ?

but not ideal for reasons you can guess (and requires special handling/escaping depending on the regex pattern).

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I believe the only way to do this is using an EXISTS

SELECT *
FROM foo
WHERE EXISTS(SELECT 1
    FROM (VALUES(bar), (baz), (qux)) v(Val)
    WHERE v.Val ~ 'ab.'
);

db<>fiddle

This may be more or less verbose and/or performant than just writing each comparison individually.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply