Reverse “expression operator ANY (array expression)”

by

The question:

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).

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.


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 Comment