Re: Count of non-null values per table column

On Fri, Aug 14, 2015 at 9:17 PM, Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> wrote:

> On Fri, Aug 14, 2015 at 6:35 PM, David Nelson <dnelson77808(at)gmail(dot)com>
> wrote:
>
>> 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:
>>
>
>
>> 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.
>>
>
> You could use this to generate the SQL:
>
> \set my_table my_real_table_name
>
> SELECT 'SELECT COUNT(*) AS total_rows, '||array_to_string(array(SELECT
> 'COUNT('||column_name::text ||') AS ' || column_name::text FROM
> information_schema.columns WHERE table_name=:'my_table'),E',\n') || ' FROM
> ' || :'my_table' || ';';
>

Oh yeah, SQL to generate SQL. Done it many times to create ETL scripts.
Don't know why I didn't think of that here. Definitely a viable option.

Thanks Ken.

>
> Cheers,
> Ken
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ <http://agency-software.org/>*
> *https://agency-software.org/demo/client
> <https://agency-software.org/demo/client>*
> ken(dot)tanzer(at)agency-software(dot)org
> (253) 245-3801
>
> Subscribe to the mailing list
> <agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.
>