Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

What query would return the name of the columns of a table where all rows are NULL?

share|improve this question
Do you mean a particular table, or all tables in a schema? – Jack Douglas May 13 '11 at 15:08
Why would you need to do that? Sounds like you have too many columns/tables and should rethink your design. – eevar May 17 '11 at 8:40


create role stack;
create schema authorization stack;
set role stack;

create table my_table as 
select generate_series(0,9) as id, 1 as val1, null::integer as val2;

create table my_table2 as 
select generate_series(0,9) as id, 1 as val1, null::integer as val2, 3 as val3;


create function has_nonnulls(p_schema in text, p_table in text, p_column in text)
                returns boolean language plpgsql as $$
  b boolean;
  execute 'select exists(select * from '||
          p_table||' where '||p_column||' is not null)' into b;
  return b;


select table_schema, table_name, column_name, 
       has_nonnulls(table_schema, table_name, column_name)
from information_schema.columns
where table_schema='stack';


 table_schema | table_name | column_name | has_nonnulls
 stack        | my_table   | id          | t
 stack        | my_table   | val1        | t
 stack        | my_table   | val2        | f
 stack        | my_table2  | id          | t
 stack        | my_table2  | val1        | t
 stack        | my_table2  | val2        | f
 stack        | my_table2  | val3        | t
(7 rows)

In addition you can get an approximate answer by querying the catalog - if null_frac is zero that indicates no nulls but should be double-checked against 'real' data:

select tablename, attname, null_frac from pg_stats where schemaname='stack';

 tablename | attname | null_frac
 my_table  | id      |         0
 my_table  | val1    |         0
 my_table  | val2    |         1
 my_table2 | id      |         0
 my_table2 | val1    |         0
 my_table2 | val2    |         1
 my_table2 | val3    |         0
(7 rows)
share|improve this answer
This is a hella old question, but folks who use spatial extensions (postgis) should note that empty spatial columns don't appear in pg_stats if they are empty at table creation. I found this out today when doing some housekeeping. I discovered that some historical aspatial tables had been imported using ogr2ogr. if there's no spatial column in the data being imported, ogr2ogr creates a geometry column full of <NULL>. My pg_stats has no geometry columns from the imported aspatial tables (it has all the other columns for those tables). Quite odd, I thought. – GT. Feb 16 at 3:51

In Postgresql, you can get the data directly from the stats:

vacuum analyze; -- if needed

select schemaname, tablename, attname
from pg_stats
where most_common_vals is null
and most_common_freqs is null
and histogram_bounds is null
and correlation is null
and null_frac = 1;

You might get a few false positives, so a recheck is in order after finding the candidates.

share|improve this answer
Do you need any other conditions than null_frac=1? – Jack Douglas May 25 '11 at 11:33
I'm not sure. null_frac presumably is a real, so it might be that it rounds to 1 in some odd cases. But even with 1 out of 10k rows, it would result in something that fits. – Denis de Bernardy May 25 '11 at 11:44

I will show you my solution in T-SQL, working for SQL Server 2008. I'm not familiar with PostgreSQL, but I hope that you'll find some guidance in my solution.

-- create test table
IF object_id ('dbo.TestTable') is not null
    DROP table testTable
create table testTable (
    id int identity primary key clustered,
    nullColumn varchar(100) NULL,
    notNullColumn varchar(100) not null,
    combinedColumn varchar(100) NULL,
    testTime datetime default getdate()

-- insert test data:
INSERT INTO testTable(nullColumn, notNullColumn, combinedColumn)
SELECT NULL, 'Test', 'Combination'
from sys.objects
union all
from sys.objects

select *
from testTable

-- FIXED SCRIPT FOR KNOWN TABLE (known structure) - find all completely NULL columns
select sum(datalength(id)) as SumColLength,
    'id' as ColumnName
from dbo.testTable
select sum(datalength(nullColumn)) as SumColLength,
    'nullColumn' as ColumnName
from dbo.testTable
select sum(datalength(notNullColumn)) as SumColLength,
    'notNullColumn' as ColumnName
from dbo.testTable
select sum(datalength(combinedColumn)) as SumColLength,
    'combinedColumn' as ColumnName
from dbo.testTable
select sum(datalength(testTime)) as SumColLength,
    'testTime' as ColumnName
from dbo.testTable

-- DYNAMIC SCRIPT (unknown structure) - find all completely NULL columns
declare @sql varchar(max) = '', @tableName sysname = 'testTable';

SELECT @sql +=
        'select sum(datalength(' + c.COLUMN_NAME + ')) as SumColLength,
    ''' + c.COLUMN_NAME + ''' as ColumnName
from ' + c.TABLE_SCHEMA + '.' + c.TABLE_NAME --as StatementToExecute
+ '
WHERE c.TABLE_NAME = @tableName;

SET @sql = left(@sql, len(@sql)-11)
print @sql;
exec (@sql);

What I did, in short, was to create a test table with 5 columns, ID and testTime being generated by identity and getdate() function, while the 3 varchar columns being the ones of interest. One will have only NULL values, one will not have any NULLs, the other will be a combined column. The final result of the script will be that the script will report the column nullColumn as having all rows NULL.

The idea was to calculate the function DATALENGTH for each column (calculates the number of bytes for a given expression). So I calculated the DATALENGTH value for each row of each column and made a SUM per column. If the SUM per column is NULL, then the complete column has NULL rows, otherwise there is some data inside.

Now you have to find the translation for PostgreSQL and hopefully a colleague will be able to help you with that. Or maybe there is a nice system view that will show how dumb I am for reinventing the wheel :-).

share|improve this answer

You need to query the information catalog for such information:

SELECT column_name FROM information_schema.columns WHERE table_name='your_table'

gives you the matching tables for your columns.

I don't have a postgres installation currently at hand but the rest should be simple

   loop over the results of the above query and foreach result
        send a COUNT(*) to the table
        if the count is null, give back the column,
                 else ignore it
   end foreach
share|improve this answer
This is working, but it's an iterative approach :-). I prefer the set based approach. – Marian May 14 '11 at 10:20

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.