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