PostgreSQL – Delete Rows Where Any Column Field is Null

postgresql

Is there a way to delete a row from a table where any of the column field is null without specifying explicitly which column is null?

I am using postgreSQL.

Here's my relation schema:

  Column    |  Type   |                              Modifiers                               
  --------------+---------+----------------------------------------------------------------------
  id           | integer | not null default  nextval('aurostat.visitor_center_id_seq'::regclass)
  date         | date    | 
  persons      | integer | 
  two_wheelers | integer | 
  cars         | integer | 
  vans         | integer | 
  buses        | integer | 
  autos        | integer |

Thanks

Best Answer

I see two ways of doing that:

With plain standard SQL, simply list all columns and combine that with an OR:

delete from the_table
where date is null
   or persons is null
   or two_wheelers is null
   or cars is null
   or vans is null
   or buses is null
   or autos is null;

Another (Postgres specific) solution is the comparison of the whole row with NOT NULL

select *
from the_table
where the_table is not null;

will return only rows where all columns are not null. You want the opposite, so you need to negate that where not (the_table is not null) The condition where the_table is null is something different - that only matches rows where all columns are null. 

delete from the_table
where not (the_table is not null);
Related Question