index ops for _int4 and trees?

From: Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my>
To: PostgreSQL-General <pgsql-general(at)postgresql(dot)org>
Subject: index ops for _int4 and trees?
Date: 2001-05-15 08:20:20
Message-ID: 3.0.5.32.20010515162020.01007c00@192.228.128.13
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hiroko 2001-05-15 08:49:36 Tiiiiiiiiiiiiime
Previous Message Lyle Lee 2001-05-15 07:24:28 NULL indicator variable