120

I currently have the the following query:

select regexp_matches(name, 'foo') from table;

How can I rewrite this so that the regex is in the where like the following (not working):

select * from table where regexp_matches(name, 'foo');

Current error message is: ERROR: argument of WHERE must be type boolean, not type text[] SQL state: 42804 Character: 29

Improve this question

1 Answer 1

Reset to default
210

Write instead:

select * from table where name ~ 'foo'

The '~' operator produces a boolean result for whether the regex matches or not rather than extracting the matching subgroups.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Is there an alternative to ~ operator? I'm using a javascript library that parses a query and unfortunately, it doesn't recognize ~ and any variants thereof.
@Martin You could try using SIMILAR TO which has stripped down regular expression support SELECT * FROM table WHERE name SIMILAR TO 'foo' See the documentation here for more details
@JoshFrankel : I read somewhere (I think on stackoverflow) that SIMILAR TO has worse performance.
@Nina Possibly, I'm not sure offhand. SIMILAR TO uses regex features but doesn't completely implement them which maybe makes it a special case and therefore less performant. I prefer the ~ regex operator as there aren't any surprises concerning what is implemented and what isn't as there are with SIMILAR TO
@JoshFrankel: what about this case ? codereview.stackexchange.com/questions/221146/… this is a question i have posted lately
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.