postgres sql - avg on a column defined as character

[babcia01]

If a column is defined as a character but it contains numeric values, can I use avg on such column or I have to do some type of a cast on the column.
Right now I get:
"unable to select an aggregate function avg(character varying"
on
select avg(a) from table;
Thanks a lot
from "Just started to use PostGres SQL"

 

[raisin96]

hi babcia01,

You'll just have to convert it to a number so that you could use the avg function.

Assuming you have this table:

Table "Test"
Attribute | Type | Modifier
-----------+--------------+----------
a | character(5) |


If you try this statement:

select avg(to_number(a,99999)) from test;

It should give you the average of the rows in it. The 99999 represents the type(9 for numbers i think) and length of your column (5 digits).

Hope this helps.

 

[babcia01]

Thanks for the answer.