11

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.
Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
Alex
  • 8,908
  • 28
  • 103
  • 157
  • 3
    Apparently that column is not a number but a character column. Do not store numbers in varchar (or char) columns. Change the datatype to integer, numeric or whatever suits you best. –  May 02 '12 at 08:49

1 Answers1

11

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;
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • in this column should I have numbers and letters, and for numbers I want calculate average and for leters counts – Alex May 02 '12 at 09:04