Search Postgresql Archives

Re: Count of non-null values per table column

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Aug 14, 2015 at 9:59 AM, John McKown <john.archie.mckown@xxxxxxxxx> wrote:
>
> David,
>
> It still came through as junk. But I reconstructed it below
>
> === original message ===
> 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 have inherited a database consisting of two related huge monolithic tables that lack referential integrity between them, or even basic data constraints. One of the problems these tables have is every single non-PK column is NULLable. I am trying to understand the information that is actually stored and used so that I can implement a (hopefully) better design. Towards that end I would like to know the count of non-null values in each column per column. In other words I would like to get the following output from a table (the numbers are totally made up):
>
> column_name | num_values
> ------------+-----------
> col1        |       5787
> ------------+-----------
> col2        |         17
> ------------+-----------
> col3        |        567
> ------------+-----------
> col4        |       5787
> ------------+-----------
> col5        |        143
> ------------+-----------
> col6        |          1
> ------------+-----------
>
> ====
>
> I assume what "num_values" contains is the _distinctly different_ number of values in "column_name" for each and every column name in a table. E.g. if "col1" contains value 'x' twice and 'y' ten times,then "num_values" would be 2, not 12. Or do you really want the 12? I'm unsure.
>

Thanks John for fixing that which microsoft screwed up, and I've switched to my gmail account for this.

For my purposes 12 would be fine. I just want to know of the total number of
rows in the table, how many have a value in each column. I guess I'm actually trying to get the complement of the number of NULLs per column in the end.

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
 
>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux