From: | Andreas Joseph Krogh <andreak(at)officenet(dot)no> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Alternative to INTERSECT |
Date: | 2007-07-31 17:30:51 |
Message-ID: | 200707311730.51280.andreak@officenet.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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%';
id
----
1
(1 row)
Is there a way to make this more efficient with another construct, or
INTERSECT the only way to accomplish the desired result?
--
Andreas Joseph Krogh
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Trutwin | 2007-07-31 18:52:22 | Re: Alternative to INTERSECT |
Previous Message | Andrew Sullivan | 2007-07-31 13:36:19 | Re: Database synchronization |