postgresql - count (no null values) of each column in a table

Question

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

Can I query this with sql or do I need a function which I have never done.

Appreciated for your help. grassu

Answer 1

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;

Returns:

SELECT count(col1), count(col2), count(col3), ...
FROM   mytbl

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.

You also need Postgres 9.0 or later for the string_agg() function. In older versions, you can substitute: array_to_string(array_agg(...), ', ').

You may wonder about the special cast 'mytbl'::regclass. Read more about object identifier types in the manual.

BTW: NULL values do not add to COUNT(col) by default.

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
...