Re: index ops for _int4 and trees?
От | Gyozo Papp |
---|---|
Тема | Re: index ops for _int4 and trees? |
Дата | |
Msg-id | 00ff01c0c59e$e09771e0$fa4bc5d5@jaguar обсуждение исходный текст |
Ответ на | index ops for _int4 and trees? (Lincoln Yeoh <lyeoh@pop.jaring.my>) |
Список | pgsql-general |
Hello, have a look at contrib/intarray directory! There is a pretty good index-support for one-dimensional integer array - solution for your 1st question. ... and it also includes two simple operator @ (= 'contains' similiar to AND) and && ( = 'overlap' similiar to OR) to checkarray values against an other array. There is a short README.intarray file telling you what to do. There is another contrib in contrib/array for more support to check array values. But I don't know this contibution can profitfrom the other's index technique. After you 've installed these contribs your query can be written: > select * from test where lineage like '{1,2,3,%'; select * from test where lineage[1:3] @ '{1,2,3}'; I think these satisfy you. Best, Papp Gyozo - pgerzson@freestart.hu ----- Original Message ----- From: "Lincoln Yeoh" <lyeoh@pop.jaring.my> To: "PostgreSQL-General" <pgsql-general@postgresql.org> Sent: 2001. május 15. 10:20 Subject: [GENERAL] index ops for _int4 and trees? > 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. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
В списке pgsql-general по дате отправления: