14

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

0

3 Answers 3

23

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

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

1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.