469,349 Members | 1,497 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,349 developers. It's quick & easy.

CREATE INDEX question (how to index on "money" field?)

QUICK VERSION: How do I create an index on a field of type "MONEY"?

-----------------------------
LONG VERSION:

I have a table with a field of type "money". I very often need to
access records by the purchase price so I thought I'd create an index
to help out my selects:

CREATE INDEX purchasepriceidx ON mytable (purchaseprice);

This results in the following:

ERROR: data type money has no default operator class for access
method "btree" You must specify an operator class for the
index or define a default operator class for the data type

So if I understand this message correctly, it means that "money" has
no comparison operators associated with it? I RTFM'd and found the 51
available operators (SELECT am.amname AS acc_method, opc.opcname AS
ops_name FROM pg_am am, pg_opclass opc WHERE opc.opcamid = am.oid
ORDER BY acc_method, ops_name), but I don't see anything here that
seems to relate to "money" type fields. Am I correct about my
assuptions? Anyone have suggestions they can share? Any simple
examples posted anywhere?

PostgreSQL 7.3.4 on i586-pc-linux-gnu, compiled by GCC 2.96

Thanks,

Stephane Charette
stephanecharette @@@ telus ... net
Nov 12 '05 #1
1 2081
Stephane Charette wrote:
QUICK VERSION: How do I create an index on a field of type "MONEY"?

-----------------------------
LONG VERSION:

I have a table with a field of type "money". I very often need to
access records by the purchase price so I thought I'd create an index
to help out my selects:

CREATE INDEX purchasepriceidx ON mytable (purchaseprice);


It's working on 7.4:

Welcome to psql 7.4beta3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

kalman=# create table test( a money );
CREATE TABLE
kalman=# create index idx on test (a );
CREATE INDEX

Regards
Gaetano Mendola






---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Chumley the Walrus | last post: by
3 posts views Thread by Robby Russell | last post: by
5 posts views Thread by Dan C Douglas | last post: by
4 posts views Thread by Jerry | last post: by
2 posts views Thread by Randy Smith | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.