Postgres Pro
Downloads
Home   >   mailing lists

Thread: Inconsistency with LIKE and SIMILAR TO

Inconsistency with LIKE and SIMILAR TO

From
Rosiński Krzysztof 2 - Detal TP
Date:
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 ?


pasman

Re: Inconsistency with LIKE and SIMILAR TO

From
Thom Brown
Date:
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

Re: Inconsistency with LIKE and SIMILAR TO

From
Tom Lane
Date:
=?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

Re: Inconsistency with LIKE and SIMILAR TO

From
Bruce Momjian
Date:
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. +