index ops for _int4 and trees?
От | Lincoln Yeoh |
---|---|
Тема | index ops for _int4 and trees? |
Дата | |
Msg-id | 3.0.5.32.20010515162020.01007c00@192.228.128.13 обсуждение исходный текст |
Список | pgsql-general |
Hi, Say I have the following table: create table test ( id int, lineage integer[] ); insert into test (id,lineage) values ('8','{1,2,3}'); insert into test (id,lineage) values ('9','{1,3,7}'); insert into test (id,lineage) values ('10','{1,2,3}'); insert into test (id,lineage) values ('11','{1,2,3,10}'); insert into test (id,lineage) values ('12','{1,3,7,9}'); 1) How do I create an index on integer[] aka _int4? 2) Is it possible to do something similar to the following select? select * from test where lineage like '{1,2,3,%'; I'm basically using this as a method of fetching rows in a particular branch of a whole tree, without having to do recursion and multiple selects. If 1 or 2 are not possible then I'll stick with using text and converting ids to zeropadded hexadecimal <sigh>. I'm thinking that there should be a quick way to do branches and trees, after all there's a btree index type, so... ;). Using text works but is rather crude, any working suggestions? Thanks, Link.
В списке pgsql-general по дате отправления: