Alternative to INTERSECT - Mailing list pgsql-sql

From Andreas Joseph Krogh
Subject Alternative to INTERSECT
Date
Msg-id 200707311730.51280.andreak@officenet.no
Whole thread Raw
Responses Re: Alternative to INTERSECT  (Josh Trutwin <josh@trutwins.homeip.net>)
Re: Alternative to INTERSECT  ("Rodrigo De León" <rdeleonp@gmail.com>)
Re: Alternative to INTERSECT  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
List 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


pgsql-sql by date:

Previous
From: Andrew Sullivan
Date:
Subject: Re: Database synchronization
Next
From: Josh Trutwin
Date:
Subject: Re: Alternative to INTERSECT