4

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

1

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.