Обсуждение: problem with array query
Hi Folks
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
CREATE TABLE tblretrain
(
pkretrainid integer NOT NULL,
mailid integer NOT NULL,
train_to smallint NOT NULL,
owners character varying(1024)[],
bayes_trained boolean DEFAULT false,
contents text NOT NULL,
CONSTRAINT tblretrain_pk PRIMARY KEY (pkretrainid)
)
The problem is that it generates an error:
ERROR: array value must start with "{" or dimension information
********** Error **********
ERROR: array value must start with "{" or dimension information
SQL state: 22P02
It seems as though postgres is not recognising owners as an array.
Any suggestions please ?
regards
Grant
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
______ 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.regardsGrantOn 28/09/2009, at 11:42 AM, Tom Lane wrote:
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 definitionowners 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
> Hi Folks
>
> 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
>
> CREATE TABLE tblretrain
> (
> pkretrainid integer NOT NULL,
> mailid integer NOT NULL,
> train_to smallint NOT NULL,
> owners character varying(1024)[],
> bayes_trained boolean DEFAULT false,
> contents text NOT NULL,
> CONSTRAINT tblretrain_pk PRIMARY KEY (pkretrainid)
> )
>
> The problem is that it generates an error:
>
> ERROR: array value must start with "{" or dimension information
> ********** Error **********
> ERROR: array value must start with "{" or dimension information
> SQL state: 22P02
>
> It seems as though postgres is not recognising owners as an array.
>
> Any suggestions please ?
> regards
> Grant
>
>
>