CODEWITHSUNDEEP
sqlpostgresql
João Pedro Reis Silva
João Pedro Reis Silva

Reputation: 39

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?

Upvotes: 0

Views: 34

Answers (1)

user330315
user330315

Reputation:

you can use num_nulls()

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

Upvotes: 4

Related Questions