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

average, postgresql, select

Solution

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;

Problem

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. ```

Original source