Finding columns that are NOT NULL in PostgreSQL

sql postgresql information-schema
Allan David Munja picture Allan David Munja · Mar 17, 2011 · Viewed 86.2k times · Source

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

Mike Sherrill 'Cat Recall' picture Mike Sherrill 'Cat Recall' · Mar 17, 2011

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;

Related questions

List all tables in postgresql information_schema

What is the best way to list all of the tables within PostgreSQL's information_schema? To clarify: I am working with an empty DB (I have not added any of my own tables), but I want to see every table …

Read More
How to check if a table exists in a given schema

Postgres 8.4 and greater databases contain common tables in public schema and company specific tables in company schema. company schema names always start with 'company' and end with the company number. So there may be schemas like: public company1 company2 company3 ... …

Read More
Behaviour of NOT LIKE with NULL values

I want to fetch all columns of a table except of columns of type serial. The closest query to this problem I was able to come up with this one: SELECT column_name FROM information_schema.columns WHERE table_name = …

Read More