You will have to retype this query using an explicit cast

[progolf069]

Greetings Techies:

I am seeking some assistance in PSQL - Typecasting.

I have a product catalog created, and I am wanting to implement a price search feature. I want to allow the customer the oppurnunity to input a range of prices (high/low) that they are willing to pay for a product. Then I will query the database, and return anything that matches their search. The problem that I am running into, is that I have the money field in the database setup as type: money. I need to be able to typecast it to a double, or some floating point value for numeric comparison, as money is a "string" datatype (for apperance on the website).

Here is what I have for results up to now:

ksonline=# select sku, yourcost from catitem where yourcost < 100.00;
ERROR:  Unable to identify an operator '<' for types 'money' and 'float8'
        You will have to retype this query using an explicit cast
ksonline=# 
ksonline=# select sku, yourcost from catitem where yourcost::real < 100.00;
ERROR:  Cannot cast type 'money' to 'float4'
ksonline=# 
ksonline=# select sku, yourcost from catitem where yourcost::double precision < 100.00;
ERROR:  Cannot cast type 'money' to 'float8'
ksonline=#

Any support you maybe able to provide will be greatly appreciated. Have a great day!

Ian Wickline
Webmaster, catalog.ksmerchandise.com

 

[progolf069]

Dummy me, I was having a "brain fart" early this morning. I guess it is just going to be one of them days!

I put the type cast on the wrong side!

The correct cast is:

  select sku, yourcost 
    from catitem 
   where yourcost < 100.00::money
order by yourcost desc;

Have a great day,
Ian

 

[rycamor]

One note: The 'money' type is deprecated in PostgreSQL. Generally, it should just be replaced with 'numeric'. -------------------------------------------

Big Brother: "War is Peace" -- Big Business: "Suspicion is Trust"
(

http://www.cl.cam.ac.uk/~rja14/tcpa-faq.html)