Geek asks and answers

PostreSQL ERROR: AVG (character change) function does not exist

I want to calculate the average of a column in PostgreSQL

SELECT AVG(col_name) 
From TableName

This 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.
+3
source share
1 answer

Store numbers in a numeric field, integer, decimal, or any other. But not in the text / varchar field.

Check the manual for all numeric data types .

Unpleasant workaround: CAST some entries to a numeric value and save 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;
+6
source

More articles:


All Articles