I would like to count the number of null's present per row in a table without enumerating column names. For example:
WITH t as (VALUES
(NULL ,'hi',2,NULL,'null'),
('' ,'hi',2,3,'test'),
(NULL ,'hi',2,3,'null')
)
SELECT countnulls(t)
FROM t;
Would result in:
numnulls
2
0
1
The closest I can get is with the following hack of row_to_json()
:
select
(CHAR_LENGTH(row_to_json(t)::text)
- CHAR_LENGTH(REPLACE(row_to_json(t)::text, 'null', '')))/4 from t;
Which is… quite the hack (not in a good way). It works, sort of, but it counts the string 'null' as a NULL when it is present in the actual data or in the column names. So it is incorrect in the above case.
Best Answer
1. You know the column names ...
For Postgres 9.6 or later, use
num_nulls()
Returns your desired result exactly, works for any mix of data types.
The manual:
For Postgres 9.5 or older, convert to
text[]
,array_remove(arr, null)
and use the remaining array length for an exact count:Any type can be cast to
text
. The cast is redundant fortext
columns, of course.array_remove()
requires Postgres 9.3 or later.cardinality()
requires Postgres 9.4 or later. Substitute witharray_length(arr, 1)
in older versions.2. You don't know column names, but Postgres does
When building on actual tables (or other registered objects like a view or a materialized view), we can retrieve column names from the system catalog
pg_attribute
to fully automate with dynamic SQL. Like:Call:
Returns the count for for each row in current physical order. Nothing else, to be absolutely generic.
Related:
We could also pass each row to return a single count for it using a polymorphic function. Related:
3. You don't know anything: anonymous records
In the unlikely event that column names are unknown even to Postgres (like from a
VALUES
expression in your example), convert to a document type (json
,jsonb
,xml
,hstore
) to get a handle, like demonstrated by ypercube (comment deleted by now) and Evan.But anonymous records do not have primary keys or any other unique attribute per definition. Count within each
LATERAL
subquery to defend against false aggregates. Demo withjsonb
:Or with
json
: probably a bit faster because the conversion is cheaper.Demonstrating 3 different ways:
db<>fiddle here