I'm wondering why a IN b isn't equivalent to a = ANY b for arrays, as it
is for subqueries.

That is, why can't you write

SELECT 1 IN ( ARRAY[1, 2, 3] );

when you can write

SELECT 1 = ANY ( ARRAY[1, 2, 3] );

?

I'm guessing that there is a semantic inconsistency between these
expressions, as the first one considers what is in parentheses as a
list, the second one as a single expression. That would be very bad.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

http://www.postgresql.org/docs/faq

Peter Eisentraut <peter_e@gmx.net> writes:
> That is, why can't you write
> SELECT 1 IN ( ARRAY[1, 2, 3] );
> when you can write
> SELECT 1 = ANY ( ARRAY[1, 2, 3] );
> ?

The two syntaxes are in fact *not* equivalent according to SQL92.
= ANY derives from

<quantified comparison predicate> ::=
<row value constructor> <comp op> <quantifier> <table subquery>

<quantifier> ::= <all> | <some>

<all> ::= ALL

<some> ::= SOME | ANY

(notice the RHS *must* be a <table subquery>) whereas IN comes from

<in predicate> ::=
<row value constructor>
[ NOT ] IN <in predicate value>

<in predicate value> ::=
<table subquery>
| <left paren> <in value list> <right paren>

<in value list> ::=
<value expression> { <comma> <value expression> }...

The form "expr = ANY (non-query-expr)" is therefore a spec extension,
which we are free to define as we wish, and we defined it to be a
scalar-vs-array-elements comparison. But I don't see any way that we
can interpret "expr IN (other-expr)" as anything except a variant
spelling for a simple equality test.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match