Alternative to INTERSECT

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

Responses

Browse pgsql-sql by date

  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