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(at)wookimus(dot)net
> a.k.a ^chewie, gunnarr http://wookimus.net/~chewie
>
> Gnupg = B4AB D627 9CBD 687E 7A31 1950 0CC7 0B18 206C 5AFD
> ----------------------------------------------------------------
>
> ************

--
rdavis(at)lillysoftware(dot)com
rsdavis(at)mediaone(dot)net
http://people.ne.mediaone.net/rsdavis