On Fri, Aug 14, 2015 at 10:00 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
>
> David Rowley <david.rowley@xxxxxxxxxxxxxxx> writes:
> > On 15 August 2015 at 02:32, David Nelson <dlnelson77808@xxxxxxxxxxx> 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.>
> David Rowley <david.rowley@xxxxxxxxxxxxxxx> writes:
> > On 15 August 2015 at 02:32, David Nelson <dlnelson77808@xxxxxxxxxxx> 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;
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
>
