From: Andreas Joseph Krogh <andreak@officenet.no>
Organization: OfficeNet AS
To: pgsql-sql@postgresql.org
Subject: Alternative to INTERSECT
User-Agent: KMail/1.9.6
MIME-Version: 1.0
Content-Disposition: inline
Date: Tue, 31 Jul 2007 17:30:51 +0000
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200707311730.51280.andreak@officenet.no>

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