postgresql PostreSQL 错误：函数 AVG（字符变化）不存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10410417/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostreSQL ERROR: Function AVG (character varying) does not exist
提问by Alex
I want to calculate the average number from a column in PostgreSQL
我想从 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.
回答by Frank Heikens
Store numbers in a numeric field, an integer, decimal or whatever. But not in a text/varchar field.
将数字存储在数字字段、整数、小数或其他任何内容中。但不是在 text/varchar 字段中。
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;
