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
