PostreSQL ERROR: Function AVG (character varying) does not exist

Question

I want to calculate the average number from a column in PostgreSQL

SELECT AVG(col_name) 
From TableName

It gives me this error:

ERROR: function avg (character varying) does not exist

LINE 1: SELECT AVG(col_name) 
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Answer 1

Store numbers in a numeric field, an integer, decimal or whatever. But not in a text/varchar field.

Check the manual for all numeric data types.

---

Nasty workaound: CAST some records to a numeric value and keep others as text. Example:

/*
create temp table foo AS
SELECT x FROM (VALUES('x'),('1'),('2')) sub(x);
*/

WITH cte AS (
SELECT
    CASE 
        WHEN x ~ '[0-9]' THEN CAST(x AS decimal) -- cast to numeric field
    END AS num,
    CASE 
        WHEN x ~ '[a-zA-Z]' THEN x
    END AS a
FROM foo
)
SELECT AVG(num), COUNT(a) FROM cte;