Using SIMILAR TO for a regex?

Question

Why is the following instruction returning FALSE?

SELECT '[1-3]{5}' SIMILAR TO '22222' ;

I can't find what is wrong with that, according to the Postgres doc ...

Answer 1

Your basic error has already been answered.
More importantly, don't use SIMILAR TO at all. It's completely pointless:

• Query performance in PostgreSQL using 'similar to'
• Difference between LIKE and ~ in Postgres

Use LIKE, or the regular expression match operator ~, or other pattern matching operators:

• Pattern matching with LIKE, SIMILAR TO or regular expressions

For 5 digits between 1 and 3 use the expression @Thomas provided.

If you actually want 5 identical digits between 1 and 3 like your example suggests I suggest a back reference:

SELECT '22222' ~ '([1-9])\1{4}';

Related answer with more explanation:

• Deleting records with number repeating more than 5

sqlfiddle demonstrating both.

Answer 2

The operator is defined as:

string SIMILAR TO pattern 

so the first parameter is the string that you want to compare. The second parameter is the regex to compare against.

You need:

SELECT '22222' SIMILAR TO '[1-3]{5}';

Answer 3

try

SELECT '22222' ~ '[1-3]{5}'

SIMILAR is not POSIX standard

The SIMILAR TO operator returns true or false depending on whether its pattern matches the given string. It is similar to LIKE, except that it interprets the pattern using the SQL standard's definition of a regular expression. SQL regular expressions are a curious cross between LIKE notation and common regular expression notation.

...

POSIX regular expressions provide a more powerful means for pattern matching than the LIKE and SIMILAR TO operators. Many Unix tools such as egrep, sed, or awk use a pattern matching language that is similar to the one described here.

http://www.postgresql.org/docs/9.2/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP