Обсуждение: repost: unique composite index with boolean fields

Hello there,

Could anybody advise me how can I create composite index for table when
one of key fields is of boolean type?

Schema is as follows:

create table test (indoor bool, pos int, name varchar(64) NOT NULL,
check (pos >= 0 and pos <=32000));

create unique index test_pkey on test (indoor, pos);

leads to error
ERROR:  Can't find a default operator class for type 16.

Finally, I use char_ops as type class:

create unique index test_pkey on test (indoor char_ops, pos);

 -- is it correct?

Sincerely,
D.Marck                                   [DM5020, DM268-RIPE, DM3-RIPN]
------------------------------------------------------------------------
*** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@rinet.ru ***
------------------------------------------------------------------------

At 20:30 +0300 on 10/08/1999, Dmitry Morozovsky wrote:


> Could anybody advise me how can I create composite index for table when
> one of key fields is of boolean type?

Which version of PostgreSQL do you have? I'm not sure about 6.5, but in
6.4.2 type "bool" was not indexable.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

On Wed, 11 Aug 1999, Herouth Maoz wrote:

HM> > Could anybody advise me how can I create composite index for table when
HM> > one of key fields is of boolean type?
HM>
HM> Which version of PostgreSQL do you have? I'm not sure about 6.5, but in
HM> 6.4.2 type "bool" was not indexable.

Well, at least when I use 'char_ops' as a comparator, index HAS been
created and SEEMS to be workable -- but i've got no time to test it
thoroughly...

Just to remind: my solution was
'create unique index t_idx on t (intfield, boolfield char_ops);'

Sincerely,
D.Marck                                   [DM5020, DM268-RIPE, DM3-RIPN]
------------------------------------------------------------------------
*** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@rinet.ru ***
------------------------------------------------------------------------