How to Find All Rows with a Null Value in Any Column Using Postgresql

Find All Rows With Null Value(s) in Any Column

In SQL Server you can borrow the idea from this answer

;WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' as ns)
SELECT *
FROM   Analytics
WHERE  (SELECT Analytics.*
        FOR xml path('row'), elements xsinil, type
        ).value('count(//*[local-name() != "colToIgnore"]/@ns:nil)', 'int') > 0

SQL Fiddle

Likely constructing a query with 67 columns will be more efficient but it saves some typing or need for dynamic SQL to generate it.

How to find all rows with a NULL value in any column using PostgreSQL

You can use NOT(<table> IS NOT NULL).

From the documentation :

If the expression is row-valued, then IS NULL is true when the row
expression itself is null or when all the row's fields are null, while
IS NOT NULL is true when the row expression itself is non-null and all
the row's fields are non-null.

So :

SELECT * FROM t;
┌────────┬────────┐
│   f1   │   f2   │
├────────┼────────┤
│ (null) │      1 │
│      2 │ (null) │
│ (null) │ (null) │
│      3 │      4 │
└────────┴────────┘
(4 rows)

SELECT * FROM t WHERE NOT (t IS NOT NULL);
┌────────┬────────┐
│   f1   │   f2   │
├────────┼────────┤
│ (null) │      1 │
│      2 │ (null) │
│ (null) │ (null) │
└────────┴────────┘
(3 rows)

How to check for null values in a row?

You can reference the table alias in the WHERE clause. The condition where the_table is not null would return the rows where all columns are not null.

The opposite of that (where at least one column is null) can be achieved by negating the expression:

select *
from the_table
where not (the_table is not null);

Looks a bit strange, but it's not the same as the_table is null - which is never true, as the reference to the table (alias) refers to an existing row. And if a row exists the the "whole row" can't be null.

This:

with the_table (col1, col2, col3) as (
values 
  (1,null,null),
  (null,2,null), 
  (null,3,4),
  (5,6,7)
  
) 
select *
from the_table 
where not (the_table is not null);

returns:

col1 | col2 | col3
-----+------+-----
   1 |      |     
     |    2 |     
     |    3 |    4

How to select rows having column value as null?

In both Postgres and SQL server,

SELECT * FROM tab WHERE is_visible is null;

If you want to select the rows for which column values are not null, then use is not null operator:

SELECT * FROM tab WHERE is_visible is not null;

PostgreSQL query rows with least null value on columns

You can:

Order rows by number of nulls (ascending)
Limit rows to 1 ( LIMIT 1 )

Your code:

SELECT *
FROM your_table
ORDER BY 
    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 +
    CASE WHEN col4 IS NULL THEN 1 ELSE 0 END 
LIMIT 1

Postgres get all columns that have no null values

I can't claim it will break any speed records, but it will do what you ask. No dynamic SQL or user-defined functions necessary.

SELECT t.*
FROM your_table as t
-- If nulls are present, these will not be equal
WHERE to_jsonb(t) = jsonb_strip_nulls(to_jsonb(t))

If performance becomes a real concern such as having to run this query many times, you could create an expression index for it. However, I would recommend normalizing your database's data model if that's the case. You may just be papering over structural defects.

CREATE INDEX nulls_detected
  ON your_table (to_jsonb(your_table) = jsonb_strip_nulls(to_jsonb(your_table)));

Further optimizations could probably be found using a bloom filter for your index.

Here's an example of this in action:

CREATE TABLE null_example (
  id serial PRIMARY KEY,
  col1 int,
  col2 text,
  col3 boolean
);

INSERT INTO null_example (col1, col2, col3) VALUES
(1, 'test1', true),
(NULL, 'test2', false),
(3, NULL, true),
(4, 'test4', NULL),
(5, 'test5', false);

Now if you run the following…

SELECT t.*
FROM null_example AS t
WHERE to_jsonb(t) = jsonb_strip_nulls(to_jsonb(t));

…you get the following output. Any rows that contain NULL column values have been omitted.

id | col1 | col2  | col3
---+------+-------+------
1  | 1    | test1 | t
5  | 5    | test5 | f

If you are trying to target columns for removal such as from an ALTER TABLE … DROP COLUMN statement, the following query can help you along the way as well.

SELECT results.key, count(*), array_agg(t.id) AS affected_ids
FROM null_example AS t
  CROSS JOIN LATERAL jsonb_each(to_jsonb(t)) AS results(key, value)
WHERE results.value = 'null'::jsonb
GROUP BY results.key

This returns:

 key | count | affected_ids
-----+-------+--------------
col2 | 1     | {3}
col3 | 1     | {4}
col1 | 1     | {2}

Select rows in postgres table where an array field contains NULL

NULL can not be compared using =. The only operators that work with that are IS NULL and IS NOT NULL.

To check for nulls, you need to unnest the elements:

select e.*
from employees e
where exists (select *
              from unnest(e.staff_managed_ids) as x(staff_id)
              where x.staff_id is null);