Re: [GENERAL] Can't I create indexes on BOOL columns?

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