postgresql - 表中每个列的count(无空值) [英] postgresql - count (no null values) of each column in a table
问题描述
我正在寻找一种方法来获取特定表中每个列的计数。应该从information_schema.columns查询列名。结果应如下所示:
I'm looking for a way to get the count of each column in a specific table. The columnnames should be queried from information_schema.columns. The result should look like this:
column_name : count
我可以使用sql查询,或者我需要一个我从来没有做过的函数。
Can I query this with sql or do I need a function which I have never done.
感谢您的帮助。
grassu
Appreciated for your help. grassu
推荐答案
此查询将创建DML语句以获取所需内容。
This query will create the DML statement to get what you want.
SELECT 'SELECT ' || string_agg('count(' || quote_ident(attname) || ')', ', ')
|| 'FROM ' || attrelid::regclass
FROM pg_attribute
WHERE attrelid = 'mytbl'::regclass
AND attnum >= 1 -- exclude tableoid & friends (neg. attnum)
AND attisdropped is FALSE -- exclude deleted columns
GROUP BY attrelid;
返回:
SELECT count(col1), count(col2), count(col3), ...
FROM mytbl
您也可以自动执行它。但不是在计划SQL中,您需要在plpgsql函数中 EXECUTE
或 DO
语句(PostgreSQL 9.0或更高版本)。
You can automatically execute it, too. But not in plan SQL, you need EXECUTE
in a plpgsql function or DO
statement (PostgreSQL 9.0 or later) for that.
您还需要Postgres 9.0或更高版本的 string_agg()
函数。在旧版本中,您可以替换: array_to_string(array_agg(...),',')
。
You also need Postgres 9.0 or later for the string_agg()
function. In older versions, you can substitute: array_to_string(array_agg(...), ', ')
.
你可能想知道特殊的'mytbl':: regclass
。详细了解手册中的对象标识符类型。
You may wonder about the special cast 'mytbl'::regclass
. Read more about object identifier types in the manual.
BTW: NULL
值不会添加到 COUNT(col)
BTW: NULL
values do not add to COUNT(col)
by default.
用 mytbl
替换(模式限定)表名。在你的情况下应该是:
Substitute the (schema-qualified) table name for mytbl
. In your case that should be:
...
WHERE attrelid = 'geoproject.mes_wastab'::regclass
...
如果您应该使用混合大小写或其他方式引号):
If you should be using mixed case or otherwise messed up identifiers (note the quotes):
...
WHERE attrelid = '"gEopRoject"."MES_wastab"'::regclass
...
这篇关于postgresql - 表中每个列的count(无空值)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!