472,378 Members | 1,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,378 software developers and data experts.

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

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


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


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


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 );
kalman=# create index idx on test (a );

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Chumley the Walrus | last post by:
I'm trying to insert a value into sql dbase, the value "amount" is a money datatype (currency). Below is the parameter for the "amount" value: cmd.Parameters.Add(New SQLParameter("@amount",...
by: Jim Hubbard | last post by:
It seems that Microsoft not only does not need the classic Visual Basic developer army (the largest army of developers the world has ever seen), but now they don't need ANY Windows developer at a...
by: Robby Russell | last post by:
Should be a simple question. When selecting a field that is of type money, how can I remove the $ when selected? example: $10.00 would return as 10.00 -Robby
by: ahaupt | last post by:
Hi all, I'm trying to get a null value into a sql money field through a SqlCommand in c#. However, I get this lovely message (which sparks loads of happy emotions in this overworked body): ...
by: Dan C Douglas | last post by:
I have just installed VS.NET 2003 on my computer. I have a project that I have been developing on VS.NET 2002. I haven't upgraded this project to VS.NET 2003 yet and I am still developing it in...
by: Jerry | last post by:
Is it possible to turn off the "$" and "," that appear in "money" formatted columns so I can dump the table in a numeric format? The man page hints that lc_monetary controls the formatting but I...
by: dmbuso | last post by:
I have a money field defined in a SQL Server 05 database with a value of 49.50. Also, it displays in SQL Server as 49.5000. I have a form in VB.NET 2005 and I'm using the new MaskedTextBox control...
by: Randy Smith | last post by:
Hi, Does anyone have any good techniques on how to convert an SQL Server 2005 "money" datatype into the ASP.Net "double" datatype? OR, should we be using "decimal" at both ends? TIA, Randy...
by: jonesgirl620 | last post by:
I want to select a filed that is declared as money and have the output of that select statement be decial point free. Do I use Cast?
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.