From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Andreas Joseph Krogh <andreak(at)officenet(dot)no> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Alternative to INTERSECT |
Date: | 2007-07-31 21:09:07 |
Message-ID: | 20070731140503.F67596@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Tue, 31 Jul 2007, Andreas Joseph Krogh wrote:
> Hi all. I have the following schema:
>
> CREATE TABLE test (
> id integer NOT NULL,
> field character varying NOT NULL,
> value character varying NOT NULL
> );
>
> ALTER TABLE ONLY test
> ADD CONSTRAINT test_id_key UNIQUE (id, field, value);
>
> CREATE INDEX test_like_idx ON test USING btree (id, field, value
> varchar_pattern_ops);
>
> Using INTERSECT I want to retrieve the rows matching (pseudo-code) "firstname
> LIKE ('andrea%' OR 'jose%') AND lastname LIKE 'kro%'"
>
> on=> SELECT t.id from test t WHERE t.field = 'firstname' AND t.value
> LIKE 'andrea%'
> INTERSECT SELECT t.id FROM test t WHERE t.field = 'firstname' AND t.value
> LIKE 'jose%'
> INTERSECT SELECT t.id FROM test t WHERE t.field = 'lastname' AND t.value
> LIKE 'kro%';
Do you want something with only a firstname of jose or a firstname of jose
and something other than andrea (and no others) to match or not? I'd read
the pseudo-code to say yes, but AFAICT the query says no.
In general, some form of self-join would probably work, but the details
depend on exactly what should be returned.
From | Date | Subject | |
---|---|---|---|
Next Message | Alessandra Bilardi | 2007-08-01 09:41:32 | could not [extend relation|write block N of temporary file|write to hash-join temporary file] |
Previous Message | Rodrigo De León | 2007-07-31 20:30:17 | Re: Alternative to INTERSECT |