Count not-null columns in row

Question

I'm trying to determine the number of specific columns that are populated, here's what I'm trying to do:

  foo := COALESCE($1.col1, '') || ' ' ||
    COALESCE($1.col2, '') || ' ' ||
    COALESCE($1.col3, '') || ' ' ||
    COALESCE($1.col4, '');
  foo := REPLACE(foo, var, ' ');
  words := string_to_array(foo, ' ');
  RETURN array_length(words, 1);

where var is white space, \s doesn't seem to work. I'm open to any other methods of finding the number of non-nil columns.

Your Postgres version should always be declared. And this is obviously PL/pgSQL code, which also needs to be declared (or readers knowing only SQL will be confused). — Erwin Brandstetter, Jan 22 '16 at 03:14
A couple more details: I'm using postgres 9.3.4.2, and the columns I'm counting are only a select few from the column. This has to be a function for performance reasons. — Josh, Jan 22 '16 at 13:51
Do you run it for just *one* known set of columns? Or do you need a function for *any* given row? Is the data type of relevant columns alyways the same? Please update your question to clarify. — Erwin Brandstetter, Jan 22 '16 at 13:54

score 2 · Answer 1 · answered Jan 22 '16 at 01:15

Something like this would work, and it's easier.

create table foo (
  id integer primary key,
  col1 text,
  col2 text,
  col3 text);

insert into foo values 
  (0, null, null, null),
  (1, null, null, 'aa'),
  (2, null, 'aa', 'bb'),
  (3, 'aa', 'bb', 'cc');

select id, 
  case when col1 is null then 1 else 0 end +
  case when col2 is null then 1 else 0 end +
  case when col3 is null then 1 else 0 end as null_columns
from foo;

Produces

SQL Fiddle: http://sqlfiddle.com/#!15/2ab3c/7/0

score 2 · Accepted Answer · edited May 23 '17 at 12:07

You can do this without spelling out all columns - or even knowing about them - with JSON functions in Postgres 9.3 or later:

SELECT t.*, count(value)::int AS notnull_ct   -- cast to int is optional
FROM   tbl t, json_each_text(row_to_json(t))  -- implicit LATERAL join
-- WHERE key LIKE 'col%' -- optionally consider only selected columns
GROUP  BY tbl_id;        -- PK column

What is the difference between LATERAL and a subquery in PostgreSQL?

json_each_text() returns (key, value) by default. Use different aliases and / or table-qualify names in case of naming conflicts. If you are only interested in selected columns, you can filter column names in a WHERE clause.

Or use the additional module hstore for the same purpose, available at least since Postgres 8.3:

Key value pair in PostgreSQL

SELECT t.*, count(v)::int AS notnull_ct
FROM   tbl t, svals(hstore(t)) v
GROUP  BY tbl_id;

The main feature is that count() does not count NULL values (and never returns NULL either). Exactly what you need.

You can encapsulate it in a function. A simple SQL function with a polymorphic input type does the job:

CREATE OR REPLACE FUNCTION f_count_notnull_in_row(ANYELEMENT)
  RETURNS int LANGUAGE sql IMMUTABLE AS
'SELECT count(value)::int
 FROM   json_each_text(row_to_json($1))';

Call:

SELECT *, f_count_notnull_in_row(t)
FROM  tbl t;

SQL Fiddle (reusing Bill's setup).

score 0 · Answer 3 · answered Jan 22 '16 at 04:38

Considering following table as an example

| id |   col1 |   col2 |   col3 |
|----|--------|--------|--------|
|  0 | (null) | (null) | (null) |
|  1 | (null) | (null) |     aa |
|  2 | (null) |     aa |     bb |
|  3 |     aa |     bb |     cc |

Using unnest() and array() to get the desired output.

SELECT id,count(col) not_null_col_cnt
FROM (SELECT id,unnest(array [col1,col2,col3]) col
      FROM foo
     ) t
GROUP BY id
ORDER BY id

Result:

| id | not_null_col_cnt |
|----|------------------|
|  0 |                0 |
|  1 |                1 |
|  2 |                2 |
|  3 |                3 |

score 0 · Answer 4 · answered Jan 22 '16 at 07:58

You could use simple casting:

SELECT id, 
 (col1 IS NULL)::int + (col2 IS NULL)::int  + (col3 IS NULL)::int  As null_number
FROM table_name;

SqlFiddleDemo

Output:

╔═════╦═════════╦═════════╦═════════╦═════════════╗
║ id  ║  col1   ║  col2   ║  col3   ║ null_number ║
╠═════╬═════════╬═════════╬═════════╬═════════════╣
║  1  ║ a       ║ b       ║ c       ║           0 ║
║  2  ║ a       ║ b       ║ (null)  ║           1 ║
║  3  ║ a       ║ (null)  ║ (null)  ║           2 ║
║  4  ║ (null)  ║ (null)  ║ (null)  ║           3 ║
╚═════╩═════════╩═════════╩═════════╩═════════════╝

Count not-null columns in row

4 Answers4