Postgres Pro
Downloads
Home   >   mailing lists

Re: Count of non-null values per table column - Mailing list pgsql-general

From David Nelson
Subject Re: Count of non-null values per table column
Date
Msg-id CANxyCUGbke4_vqm-w8guaSXD5m7EX-2wx=snYZicZOr5SsmsEg@mail.gmail.com
Whole thread Raw
In response to Re: Count of non-null values per table column  (Ken Tanzer <ken.tanzer@gmail.com>)
List pgsql-general
Tree view
On Fri, Aug 14, 2015 at 9:17 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
On Fri, Aug 14, 2015 at 6:35 PM, David Nelson <dnelson77808@gmail.com> wrote:
On Fri, Aug 14, 2015 at 10:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> David Rowley <david.rowley@2ndquadrant.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
--
learn more about AGENCY or
follow the discussion.

pgsql-general by date:

Previous
From: Ken Tanzer
Date:
Subject: Re: Count of non-null values per table column
Next
From: Aviel Buskila
Date:
Subject: Re: repmgr won't update witness after failover