58

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?

informatik01
  • 16,038
  • 10
  • 74
  • 104
Allan David Munja
  • 581
  • 1
  • 4
  • 3
  • I think the question is getting at whether or not the column was set NOT NULL explicitly or if it is a primary key (which can never be null). If so, it does not look like COLUMNS has that info. May have to search through the column def in pg_attrdef for NOT NULL. Otherwise, it is just restating your first query. – nate c Mar 17 '11 at 22:10
  • It looks like COLUMNS has that information to me. Declaring a column either `NOT NULL PRIMARY KEY` or simply `PRIMARY KEY` sets is_nullable to 'NO'. Columns explicitly declared NULL and implicitly declared NULL sets is_nullable to 'YES'. – Mike Sherrill 'Cat Recall' Mar 18 '11 at 01:11

3 Answers3

71

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;
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • 13
    For those googlers who were looking to list all columns with a `NOT NULL` constraint: `SELECT table_name, column_name FROM information_schema.columns WHERE table_schema = 'public' AND is_nullable = 'NO';`. Change `table_schema = 'public'` as needed. – Daniel Waltrip Jun 17 '18 at 01:11
3

To get a count of all NOT NULL columns:

SELECT count(*)
  FROM information_schema.columns
  WHERE  table_name  = 'table_name'
     AND is_nullable = 'NO';
Taw HK
  • 321
  • 5
  • 8
2

To get a count of all NOT NULL columns in any table, use:

SELECT count(*)
  FROM information_schema.columns
 WHERE table_schema = 'table_schema_here'
   AND table_name   = 'table_name_here'
   AND is_nullable = 'YES';

I hope this will help someone.

funnybunny
  • 117
  • 1
  • 12