Re: the IN clause saga

On Tue, 22 Jul 2003, Joe Conway wrote:

> Fernando Nasser wrote:
> >> I'm not sure if this makes sense, but could you have a conflict
> >> between a set containing a single element that is the array and a
> >> set containing the elements of the array.
> >
> > You will need to have an Array of Arrays in that case.
>
> Sorry for jumping in but it doesn't seem that everyone understands the
> new functionality Tom mentioned, so I thought I might elaborate.
>
> The new syntax is:
>    <scalar> <op> ANY | SOME | ALL (<array-expression>)
> or specific to this discussion
>    <scalar> = ANY (<array-expression>)
> *not*
>    <scalar> IN (<array-expression>)
> for exactly the reason above. If the latter were allowed, it would
> present a conflict, because
>    <scalar> IN (<list-of-scalars>)
> is allowable. The former is not ambiguous because
>    <scalar> = ANY (<list-of-scalars>)
> is not, and never has been allowed. E.g.:
>
> regression=# select 1 where 1 = any (array[1,2,3]);
>   ?column?
> ----------
>          1
> (1 row)
>
> regression=# select 1 where 1 = any (1,2,3);
> ERROR:  syntax error at or near "," at character 26
>
> regression=# select 1 where 1 in (1,2,3);
>   ?column?
> ----------
>          1
> (1 row)
>

Perhaps these cases would also be illustrative as I believe that is what
Fernando was suggesting.

template1=# select 1 where 1 in (array[1,2,3]);
ERROR:  Unable to identify an operator '=' for types 'integer' and 'integer[]'
        You will have to retype this query using an explicit cast
IN:  op_error (parse_oper.c:608)
ERROR:  Unable to identify an operator '=' for types 'integer' and 'integer[]'
        You will have to retype this query using an explicit cast

template1=# select 1 where 1 in ([1,2,3]);
ERROR:  syntax error at or near "["
IN:  yyerror (scan.l:596)
ERROR:  syntax error at or near "[" at character 22


Kris Jurka



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html