Finding columns that are NOT NULL in PostgreSQL

Question

I had an assignment for each table to count nullable columns. Easy:

 SELECT table_name, count(*) FROM INFORMATION_SCHEMA.COLUMNS  WHERE is_nullable='NO'  GROUP BY table_name; 

Now I have to modify this to count "columns that have property "NOT NULL"". Will the following code do this or will it just check weather column name is not null?

CREATE TEMP TABLE A AS  SELECT DISTINCT column_name, table_name AS name FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name IS NOT NULL GROUP BY table_name, column_name;  SELECT name, count(*) FROM A GROUP BY name; 

If no... Any advices?

Answer 1

No.

This query

SELECT DISTINCT column_name, table_name FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name IS NOT NULL 

will return all the rows that have a value in the column "column_name".

All rows in that table will always have a value in the column "column_name".

Do you just need to know how many columns are nullable and how many are non-nullable?

SELECT is_nullable, COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS GROUP BY is_nullable; 

Count by table name? I think you can use this.

SELECT table_name, is_nullable, count(*) FROM INFORMATION_SCHEMA.COLUMNS GROUP BY table_name, is_nullable ORDER BY table_name, is_nullable;