Inconsistency with LIKE and SIMILAR TO

2010/6/15 Rosiński Krzysztof 2 - Detal TP <
Krzysztof.Rosinski2@telekomunikacja.pl>

Hello.

Operators LIKE and SIMILAR TO work differently
This query works ok.:

SELECT *
FROM www.test
WHERE expr like any (ARRAY['a','b']);

But this not work:

SELECT *
FROM www.test
WHERE expr similar to any (ARRAY['a','b']);

ERROR: syntax error at or near "any"
LINE 3: WHERE expr similar to any (ARRAY['a','b']);

Is this shouldn't works ?

SIMILAR TO and LIKE aren't synonyms for each other. SIMILAR TO uses a cross
between LIKE syntax and regular expressions for the evalutated expression.
I'm not entirely sure what your query is supposed to be doing. I thought
ANY was a keyword denoting a row-wise comparison?

Thom

=?iso-8859-2?Q?Rosi=F1ski_Krzysztof_2_-_Detal_TP?= <Krzysztof.Rosinski2@telekomunikacja.pl> writes:

Operators LIKE and SIMILAR TO work differently

Yup. It's an implementation restriction (see the comment for
subquery_Op in gram.y if you want to know).

regards, tom lane

Thom Brown wrote:

2010/6/15 Rosi?ski Krzysztof 2 - Detal TP <
Krzysztof.Rosinski2@telekomunikacja.pl>

Hello.

Operators LIKE and SIMILAR TO work differently
This query works ok.:

SELECT *
FROM www.test
WHERE expr like any (ARRAY['a','b']);

But this not work:

SELECT *
FROM www.test
WHERE expr similar to any (ARRAY['a','b']);

ERROR: syntax error at or near "any"
LINE 3: WHERE expr similar to any (ARRAY['a','b']);

Is this shouldn't works ?

SIMILAR TO and LIKE aren't synonyms for each other. SIMILAR TO uses a cross
between LIKE syntax and regular expressions for the evalutated expression.
I'm not entirely sure what your query is supposed to be doing. I thought
ANY was a keyword denoting a row-wise comparison?

LIKE is only similar to SIMILAR TO. ;-)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +