gpt4 book ai didi

postgresql - PostgreSQL 错误 : Function AVG (character varying) does not exist

转载 作者:行者123 更新时间:2023-11-29 11:32:23 27 4
gpt4 key购买 nike

我想计算 PostgreSQL 中某列的平均数

SELECT AVG(col_name) 
From TableName

它给我这个错误:

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.

最佳答案

将数字存储在数字字段、整数、小数或其他任何内容中。但不是在 text/varchar 字段中。

查看所有numeric data types的手册.


讨厌的解决方法:将一些记录转换为数值并将其他记录保留为文本。示例:

/*
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;

关于postgresql - PostgreSQL 错误 : Function AVG (character varying) does not exist,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10410417/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com