Delete from table rows where any of the column field is null

Question

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

Answer 1

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);

Answer 2

If you don't want to specify each column you could use NOT EXISTS ... NATURAL JOIN.

Warning! This solution is not best from performance point of view. It should work on Oracle/PostgreSQL/SQLite/MariaDB 10.3.2 and above.

Setting up:

CREATE TABLE the_table(
 id integer not null 
 ,date_ date 
 ,persons integer 
 ,two_wheelers integer 
 ,cars integer 
 ,vans integer 
 ,buses integer 
 , autos integer 
);
INSERT INTO the_table(id, date_, persons, two_wheelers, cars, vans, buses, autos)
VALUES (1, '21/JAN/2018',1,1,1,1,1,1);
INSERT INTO the_table(id, date_, persons, two_wheelers, cars, vans, buses, autos)
VALUES (2, '21/JAN/2018',2,2,2,2,NULL,2);
INSERT INTO the_table(id, date_, persons, two_wheelers, cars, vans, buses, autos)
VALUES (3, '21/JAN/2018',3,3,3,3,NULL,NULL);
SELECT * FROM the_table;
+----+-------------+---------+--------------+------+------+-------+-------+
| id | date_ | persons | two_wheelers | cars | vans | buses | autos |
+----+-------------+---------+--------------+------+------+-------+-------+
| 1 | 21/JAN/2018 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 21/JAN/2018 | 2 | 2 | 2 | 2 | null | 2 |
| 3 | 21/JAN/2018 | 3 | 3 | 3 | 3 | null | null |
+----+-------------+---------+--------------+------+------+-------+-------+

And query:

DELETE FROM the_table
WHERE NOT EXISTS (SELECT *
 FROM the_table t1
 NATURAL JOIN the_table t2
 WHERE id = the_table.id);

Output:

+----+-------------+---------+--------------+------+------+-------+-------+
| id | date_ | persons | two_wheelers | cars | vans | buses | autos |
+----+-------------+---------+--------------+------+------+-------+-------+
| 1 | 21/JAN/2018 | 1 | 1 | 1 | 1 | 1 | 1 |
+----+-------------+---------+--------------+------+------+-------+-------+

DBFiddle Demo

Answer 3

Took me a moment as I couldn't find a function to actually do the job anywhere...

CREATE OR REPLACE FUNCTION delete_null_columns(table_name text)
RETURNS void AS $$
DECLARE
 column_name text;
 sql_query text;
 row_count integer; -- Add this line to declare the row_count variable
BEGIN
 -- Get the list of column names and iterate over them
 FOR column_name IN
 SELECT columns.column_name
 FROM information_schema.columns
 WHERE columns.table_name = delete_null_columns.table_name
 AND table_schema = 'public'
 LOOP
 -- Generate a query to check if the column contains only null values
 sql_query := format(
 'SELECT COUNT(*) FROM %I.%I WHERE %I IS NOT NULL',
 'public', delete_null_columns.table_name, column_name
 );
 EXECUTE sql_query INTO row_count;
 -- If the column contains only null values, delete it
 IF row_count = 0 THEN
 sql_query := format(
 'ALTER TABLE %I.%I DROP COLUMN %I',
 'public', delete_null_columns.table_name, column_name
 );
 EXECUTE sql_query;
 END IF;
 END LOOP;
END;
$$ LANGUAGE plpgsql;