Select * with result columns that only contain values-CodePudding

postgresql --I don't want to have to enter each repeating column, I want syntax in the where clause that checks all the columns and returns only columns with data

SELECT *
FROM table_with_200_columns
where datetime between '2021-01-01' and current_date
and column.1 is not null
and column.1 <>''
and column.2 is not null
and column.2 <>''
and column.3-200 is not null
and column.3-200 <>''
;
--something like this with an "presently unknown function" as in the example of 'allofthemtherecolumns'
SELECT *
FROM table_with_200_columns
where datetime between '2021-12-01' and current_date
and allofthemtherecolumns is not null
and allofthemtherecolumns <>''

your assistance is greatly appreciated

CodePudding user response：

You can transform a row into an hstore record, then extract just the values and look if none of them is null

select * from t 
where false = ALL (SELECT unnest(avals(hstore(t))) IS NULL);

PS: you would need the hstore extension

CodePudding user response：

Convert the table row to json, remove datetime and all null/blank valued key, then check it's not empty:

select *
from table_with_200_columns
where datetime between '2021-01-01' and current_date
and regexp_replace(jsonb_strip_nulls(to_jsonb(table_with_200_columns) - 'datetime')::text, '"[^"] ":"",?', '', 'g') != '{}'