| 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: | Whole Thread | Raw Message | 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 |