CODEWITHSUNDEEP
sqlpostgresql
Meir Tseitlin
Meir Tseitlin

Reputation: 2068

removing null rows, where columns are dynamically subselected

I have a table with high number of value columns which looks like:

CREATE TABLE tbl ("timestamp" timestamp PRIMARY KEY, "a" real, "b" real, "c" real)

The table may contain a dynamic number of value columns (like a,b,c).

I need to delete rows with where all values are null, but timestamp is not null.

I am unable to build a select query which will return null rows only.

Doing something like:

select * 
from tbl 
where tbl is null

will not work, because timestamp is not null

I tried to take the following working example:

select * 
from tbl 
where not (a,b,c) is null

and add a subselect to it:

select * 
from tbl 
where not (
    SELECT string_agg(column_name, ',')
    FROM information_schema.columns
    WHERE table_name   = 'tbl' and column_name != 'timestamp'
) is null

But it is not working.

Upvotes: 0

Views: 44

Answers (1)

user330315
user330315

Reputation:

You can convert the row to a JSONB object, remove the "timestamp" column and then check for an empty value:

select *
from tbl 
where jsonb_strip_nulls(to_jsonb(tbl) - 'timestamp') = '{}'::jsonb;

This can directly be used to delete the rows:

delete from tbl
where jsonb_strip_nulls(to_jsonb(tbl) - 'timestamp') = '{}'::jsonb);

Upvotes: 3

Related Questions