Select columns with null values postgresql

Question

I'm working on a postgreSQL database with 22 table. I need a query which returns the columns with null values. May be a static sql statement that I can launch to each table.

I would be pleased to get some help.

Best.

Are you looking for dynamic SQL here? Can you post your database information? Also, by "database" did you really mean "table?" — Tim Biegeleisen
– Tim Biegeleisen, Commented May 18, 2018 at 11:19
I need one query which returns all the null columns of one table — zakaria mouqcit
– zakaria mouqcit, Commented May 18, 2018 at 11:30

Michel Milezzi · Accepted Answer · 2018-05-18 14:24:27Z

2

Assuming that you run VACUUM ANALYZE periodically, pg_stats.null_frac can help you to get that:

--Get columns "filled" entirely with null values
SELECT 
    schemaname,
    tablename,
    attname,
    null_frac 
FROM 
    pg_stats 
WHERE 
    null_frac = 1.0 
    AND schemaname = 'yourschema'

edited May 18, 2018 at 14:24

answered May 18, 2018 at 11:59

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

zakaria mouqcit Over a year ago

Thank you for you response it does give the columns with null values. If it's possible I want to know if there is a possibility to have the count of these null values per each column?

Michel Milezzi Over a year ago

You could multiply pg_stats.null_frac by pg_stat_user_tables.n_live_tup to get an estimative.

zakaria mouqcit Over a year ago

I did it but doesn't give any result!

Michel Milezzi Over a year ago

@zakariamouqcit please create another question so we could help you there

zakaria mouqcit Over a year ago

solved the issue, thank you for your assistance and help.

|

Collectives™ on Stack Overflow

Select columns with null values postgresql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related