Thread: Can't I create indexes on BOOL columns?

Hello,

I am using PostgreSQL 6.4.2.  and it seems I can't create indexes on BOOL
columns.  I tried this:

$ psql template1
template1=> CREATE DATABASE test\g
CREATEDB
template1=> CREATE TABLE users (id INT DEFAULT 0 NOT NULL,name TEXT,preferred_name TEXT,email TEXT,alias VARCHAR (12)
DEFAULT'0' NOT NULL,password TEXT,reminder TEXT,notify_any CHAR (1) DEFAULT 't' NOT NULL,notify_new CHAR(1) DEFAULT 't'
NOTNULL,notify_changed CHAR(1) DEFAULT 't',edit_users CHAR(1) DEFAULT 'f')\g 
CREATE
template1=> CREATE INDEX users_notify ON users (notify_any,notify_new)\g
ERROR:  Can't find a default operator class for type 16

However, if I change the BOOL type to CHAR (1) it works.

Is this a bug? A configuration problem? What?

Regards,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org
--
E-mail: mlemos@acm.org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--

On 13 Dec 1999, Manuel Lemos wrote:

> Hello,
>
> I am using PostgreSQL 6.4.2.  and it seems I can't create indexes on
> BOOL columns.  I tried this:
>
> ...snip...
>
> However, if I change the BOOL type to CHAR (1) it works.

I'm not a developer, so I don't know if it's a bug or a logic error
you're pointing out, or even if it's an error at all.  However, I could
rationalize that it is not an error on the premise that BOOLEAN
identifiers can only have two values: true or false.

Obviously, you cannot use a UNIQUE index on such an column.  A Clustered
index would not do much better, logically, since you're only splitting
up the data into two groups.  Therefore, what is the point of indexing
on boolean columns, really?  I suppose it would depend upon the data in
the table.  If you see one value predominantly over the other, splitting
out the smaller group via the index *may* help your search.  However, if
the values are fairly even in proportion, you won't gain much.

I'd say stick with your work-around, and post the question to the devel
team.

----------------------------------------------------------------
Chad Walstrom                         mailto:chewie@wookimus.net
a.k.a ^chewie, gunnarr               http://wookimus.net/~chewie

   Gnupg = B4AB D627 9CBD 687E 7A31  1950 0CC7 0B18 206C 5AFD
----------------------------------------------------------------

Hi I have a similar problem. I cant create indexes on decimal columns.

CREATE TABLE REQUIREMENT_COST(
 WORKORDER_TYPE CHAR(1) NOT NULL,
 WORKORDER_BASE_ID VARCHAR(30) NOT NULL,
 WORKORDER_LOT_ID VARCHAR(3) NOT NULL,
 WORKORDER_SPLIT_ID VARCHAR(3) NOT NULL,
 WORKORDER_SUB_ID VARCHAR(3) NOT NULL,
 OPERATION_SEQ_NO SMALLINT NOT NULL,
 REQ_PIECE_NO SMALLINT NOT NULL,
 QTY DECIMAL(14,4) NOT NULL,
 UNIT_COST DECIMAL(14,4) NOT NULL,
 FIXED_COST DECIMAL(15,2) NOT NULL,
 MINIMUM_COST DECIMAL(15,2),
 PRIMARY
KEY(WORKORDER_TYPE,WORKORDER_BASE_ID,WORKORDER_LOT_ID,WORKORDER_SPLIT_ID,WORKORDER_SUB_ID,OPERATION_SEQ_NO,REQ_PIECE_NO,QTY
))
;
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'requirement_cost_pkey' for table 'requirement_cost'
ERROR:  Can't find a default operator class for type 1700.

If the field qty is deleted from the above primary key the create works.
I assume that there is an operator I can add to fix this?

bob


^chewie wrote:

> On 13 Dec 1999, Manuel Lemos wrote:
>
> > Hello,
> >
> > I am using PostgreSQL 6.4.2.  and it seems I can't create indexes on
> > BOOL columns.  I tried this:
> >
> > ...snip...
> >
> > However, if I change the BOOL type to CHAR (1) it works.
>
> I'm not a developer, so I don't know if it's a bug or a logic error
> you're pointing out, or even if it's an error at all.  However, I could
> rationalize that it is not an error on the premise that BOOLEAN
> identifiers can only have two values: true or false.
>
> Obviously, you cannot use a UNIQUE index on such an column.  A Clustered
> index would not do much better, logically, since you're only splitting
> up the data into two groups.  Therefore, what is the point of indexing
> on boolean columns, really?  I suppose it would depend upon the data in
> the table.  If you see one value predominantly over the other, splitting
> out the smaller group via the index *may* help your search.  However, if
> the values are fairly even in proportion, you won't gain much.
>
> I'd say stick with your work-around, and post the question to the devel
> team.
>
> ----------------------------------------------------------------
> Chad Walstrom                         mailto:chewie@wookimus.net
> a.k.a ^chewie, gunnarr               http://wookimus.net/~chewie
>
>    Gnupg = B4AB D627 9CBD 687E 7A31  1950 0CC7 0B18 206C 5AFD
> ----------------------------------------------------------------
>
> ************

--
rdavis@lillysoftware.com
rsdavis@mediaone.net
http://people.ne.mediaone.net/rsdavis