Re: Count of non-null values per table column

On Fri, Aug 14, 2015 at 10:00 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> > On 15 August 2015 at 02:32, David Nelson <dlnelson77808(at)outlook(dot)com>
wrote:
> >> Hello list,<br><br>Apologies if this has been asked before. My search
only
> >> turned up ways to list the total non-null values for all columns as a
> >> single number. I want the count for each column by column.
>
> > I assume the tables are quite large if you don't want to just issue a:
> > SELECT COUNT(a) FILTER(WHERE a IS NULL),COUNT(b) FILTER(WHERE b IS NULL)
> > ... (assuming you're on a version new enough to support agg FILTER)
>
> AFAIK this should work in any version, or indeed any SQL-compliant DBMS:
>
> select count(col1), count(col2), ... from table;

Thanks Tom (I've switched to my gmail account for this conversation). Tthat
is the way I would do it for a table with a small number of columns, but
these have several dozen so this would get tedious. Although I just
realized I could output the list of column names from the pg_stat view to a
file and whip up some vi find and replace to create the entire statement
pretty quickly. I was just wondering if that was the only way or not.

BTW, aplologies for omitting basic info:
SELECT version();
version

----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.18 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-11), 64-bit

>
> COUNT with an argument counts the non-null values of that argument.
>
> > On the other hand if you were happy with just an approximation then you
> > could look at pg_stats;
>
> Yeah; you might want to ANALYZE the table first to be sure the stats are
> up to date.
>
> regards, tom lane
>