Re: problem with array query

[Grant Maxwell <grant.maxwell@xxxxxxxxxxxx>]

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@xxxxxxxxxxxx> 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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general