problem with array query

Grant Maxwell <grant.maxwell@maxan.com.au> writes:

According to the 8.3 docs I should be able to write:
select * from tblretrain where 'ms-ap-t2-02c9' NOT IN (owners);
where owners is an array per the following definition
owners character varying(1024)[],

No, what you can write is "<> ALL", not NOT IN.

It seems as though postgres is not recognising owners as an array.

It's trying to parse the literal as an array so that it can do a
plain equality comparison against the owners column.

You probably read the part of the docs where it says that
x NOT IN (SELECT ...) is equivalent to x <> ALL (SELECT ...).
Which is true, but it has nothing to do with the non-sub-SELECT syntax.
Without a sub-SELECT, we have two cases:
x NOT IN (y,z,...) expects x,y,z to all be the same type.
x <> ALL (y) expects y to be an array of x's type.
Got it?

regards, tom lane

Hi Tom

The bit I was reading is

http://www.postgresql.org/docs/8.3/interactive/arrays.html#AEN6019
______ EXTRACT ________
However, this quickly becomes tedious for large arrays, and is not
helpful if the size of the array is uncertain. An alternative method
is described in Section 9.20. The above query could be replaced by:

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
In addition, you could find rows where the array had all values equal
to 10000 with:

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
________ END EXTRACT __________
(section 9.20 is the bit that suggests the syntax I was trying)
<> ALL is not working. I thought it would fail if the LS does not
match every array member of the RS.
What I'm trying to do is find every record where "my name" is not in
the array.
So I tried <> ANY and also <> ALL and both returned an empty row set.
regards
Grant

On 28/09/2009, at 11:42 AM, Tom Lane wrote:

Grant Maxwell <grant.maxwell@maxan.com.au> writes:

What I'm trying to do is find every record where "my name" is not in
the array.
So I tried <> ANY and also <> ALL and both returned an empty row set.

Maybe you have some nulls in the arrays? <> ALL works for me.

regards, tom lane

I don't know about Postgres 8.3, but with 8.4-docs the Syntax of your
query could be

select * from tblretrain where NOT ('ms-ap-t2-02c9' = ANY (owners));

regards
Ludwig

Grant Maxwell schrieb: