Thread: array weirdity

How can these both be true? Is there a way to see if a value is not in
an array?


select 1229 <> any('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result

result

-------

t


select 1229 = any('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result

result

-------

t

Sim Zacks wrote:
> How can these both be true? Is there a way to see if a value is not in
> an array?
>
>
> select 1229 <> any('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result
>
> result
> -------
> t

Not "any()", "all()" - it *is* different from some of the numbers there.

--
  Richard Huxton
  Archonet Ltd

On Sep 3, 2009, at 9:19 , Sim Zacks wrote:

> How can these both be true? Is there a way to see if a value is not in
> an array?
>
>
> select 1229 <> any('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result
>
> result
>
> -------
>
> t
>

Here you're comparing 1229 to each element in the array in turn, and
returning TRUE if ANY of those comparisons are TRUE. As 1229 <> 1220
(for example), it's TRUE.

> select 1229 = any('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result
>
> result
>
> -------
>
> t

Again, you're comparing 1229 to each element in the array in turn,
returning TRUE if ANY of the comparisons are TRUE. AS 1229 is an
element in the  array, the result is TRUE.

You're probably looking for ALL

SELECT 1229 <> ALL('{1220,0,0,1228,1229,1231,0,0,0}'::int[]) as result;
  result
--------
  f
(1 row)

or use NOT

SELECT NOT (1229 = ANY('{1220,0,0,1228,1229,1231,0,0,0}'::int[])) as
result;
  result
--------
  f
(1 row)

Michael Glaesemann
grzm seespotcode net