Alternative to INTERSECT

Поиск
Список
Период
Сортировка
От Andreas Joseph Krogh
Тема Alternative to INTERSECT
Дата
Msg-id 200707311730.51280.andreak@officenet.no
обсуждение исходный текст
Ответы 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>)
Список 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 по дате отправления:

Предыдущее
От: Andrew Sullivan
Дата:
Сообщение: Re: Database synchronization
Следующее
От: Josh Trutwin
Дата:
Сообщение: Re: Alternative to INTERSECT