Thread: how to index a numeric(12,2) column?

I'd like to create an index on a column whose type is NUMERIC(12,2).
There appears to be no default operator class for the numeric type.  What
class should I use instead?  My guess is that something like this might
work:

CREATE INDEX foo_idx on foo (bar int8_ops);

Will that work properly?  Will it do me any good?  Is there a better way?

Forest Wilkinson <fspam@home.com> writes:
> I'd like to create an index on a column whose type is NUMERIC(12,2).
> There appears to be no default operator class for the numeric type.

Uh, what version are you using?  Works fine for me in 7.0.2.
        regards, tom lane

>> I'd like to create an index on a column whose type is NUMERIC(12,2).
>> There appears to be no default operator class for the numeric type.
>
>Uh, what version are you using?  Works fine for me in 7.0.2.

Sorry; I hit send before adding that information.  I'm using postgres
6.5.3 on i386 Red Hat 6.1.

It allows me to create an index on a NUMERIC(12,2) field using the
int8_ops class, but I'm wondering if this might have some undesirable
hidden side effects.

CREATE TABLE foo (id INTEGER, bar NUMERIC(12,2));
CREATE INDEX foo_idx on foo (bar int8_ops);

Forest Wilkinson <fspam@home.com> writes:
> Sorry; I hit send before adding that information.  I'm using postgres
> 6.5.3 on i386 Red Hat 6.1.

Time to update, then.

> It allows me to create an index on a NUMERIC(12,2) field using the
> int8_ops class, but I'm wondering if this might have some undesirable
> hidden side effects.

Yes, and they won't be too hidden either: it won't work :-(

Current sources check for that sort of type mismatch, but 6.5 failed to
do so.
        regards, tom lane