-1

I have a LOT of junk columns in my table containing thousands of null values and maybe one or two real values. I would like to select only the columns in this table containing more than 10 non-null values.

GMB
  • 216,147
  • 25
  • 84
  • 135
D.M.
  • 1
  • 3
  • 1
    You haven't actually asked a question here - what is stopping you from accomplishing the stated requirement? What problem do you have with your existing query / research? – Stu Oct 31 '22 at 17:04
  • There's no built in `SELECT WHERE BUNCH OF STUFF IS NOT NULL`. You need to do your own select operation, like `SELECT CASE x IS NULL THEN 1 ELSE 0 END + CASE ... END` and establish some kind of threshold. – tadman Oct 31 '22 at 17:04
  • Stu, what's stopping me is i don't know how to form the query obviously? if you're that interested, I've tried selecting a count(distinct column_name) greater than a certain value, but only on a singular column. there isn't a great way for me to do this to hundreds of columns. – D.M. Oct 31 '22 at 17:09
  • Thanks tadman, I think that points me in the right direction. – D.M. Oct 31 '22 at 17:14
  • Not specifically Postgres but [this post](https://dba.stackexchange.com/questions/140647/select-only-rows-with-at-least-2-non-missing-values) might be the same question? – Stu Oct 31 '22 at 17:14

1 Answers1

1

Postgres provides the function num_nonnulls() which does exactly what you are looking for. Something like:

select * 
  from some_table 
 where num_nonnulls( col1, col2, ...) > 10;

There is also a corresponding num_nulls() function. Yes, the downside is you must list each column name.

Belayer
  • 13,578
  • 2
  • 11
  • 22