Average value for Money Datatype

The following is the “pc” table which contains details about pc.

user=> SELECT * FROM pc;
 code |  model   | speed | ram  |  hd  | cd  |  price  
------+----------+-------+------+------+-----+---------
  101 | Imac     |  2000 | 4096 |  500 | 16x | ₹550.00
  102 | G450     |  1500 | 2048 |  500 | 8x  | ₹450.00
(2 rows)
user=>

Now I want to take the average value of price. So tried the following way. But it produce error.

user=> SELECT AVG(price) FROM pc;
ERROR:  function avg(money) does not exist
LINE 1: SELECT AVG(price) FROM pc;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
user=>

So, what is the way to get the average value of the price column which is in money datatype.

Answer

SELECT AVG(price::numeric) FROM pc;

As per PostgreSQL 8 .4’s Monetary Types

select avg(regexp_replace(price::text, '[$,]', '', 'g')::numeric) from pc

Advertisement

Answer