postgres - smart way to select subquery but discard rows where all columns are empty-CodePudding

I have a rather large subquery where i want to discard rows where all the values of the subquery (not id) is null

original table:

original tablee1

desired output:

Query:

SELECT 
  id, num1, num2
FROM table
WHERE COALESCE(num1, num2) IS NOT NULL

The query above does the job, but i wondering if there was a smarter / more elegant way of writing this, which does the same as the query above. Since the actual table in question has a lot of columns, i would like avoid, in the future, having to edit a long list of columnnames in a where clause.

CodePudding user response：

Another option is to check if a record of those two columns isn't null:

select *
from the_table
where not (num1, num2) is null;

(num1, num2) is null is true if all fields of the (anonymous) record are null.

Note that it's important to negate the condition, it's not possible to use is not null - (num1, num2) is not null would not work.

I think it's a matter of personal taste which one to use.

Another more dynamic way is to convert the row to a JSON value, remove the irrelevant columns, then remove all null values and compare that to an empty object:

select *
from the_table t
where jsonb_strip_nulls(to_jsonb(t) - 'id' - 'num3') <> '{}'