'Is it possible to use WHERE with ANY and ARRAY together?

I have a table called 'reviews' that I want to start the data cleaning process, so I want to find out if there are any records that have any NULL columns (there are 6 columns in the table).

So, instead of using the code below:

SELECT *  
FROM reviews  
WHERE listing_id IS NULL  
    OR id IS NULL  
    OR date IS NULL  
    OR reviewer_id IS NULL  
    OR reviewer_name IS NULL  
    OR comments IS NULL  

'''

I would like to simplify the code, using something like this:

SELECT *  
FROM reviews  
WHERE ANY( ARRAY [listing_id, id, date, reviewer_id, reviewer_name, comments]) IS NULL

But It doesn't work.

  1. What's wrong with the second code?
  2. Are there other more efficient ways to check?
sqlpostgresql


Solution 1:[1]

you can use num_nulls()

where num_nulls(listing_id, id, date, reviewer_id, reviewer_name, comments) > 0

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 a_horse_with_no_name

Related Questions