postgresql --我不想輸入每個重復列，我希望 where 子句中的語法檢查所有列并僅回傳包含資料的列
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 <>''
非常感謝您的幫助
uj5u.com熱心網友回復：
您可以將一行轉換為 hstore 記錄，然后僅提取值并查看它們是否為空
select * from t
where false = ALL (SELECT unnest(avals(hstore(t))) IS NULL);
PS：你需要 hstore 擴展
uj5u.com熱心網友回復：
將表行轉換為 json，洗掉datetime和所有空/空值鍵，然后檢查它不為空：
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') != '{}'
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/384918.html
標籤：PostgreSQL的 选择 筛选 清楚的
上一篇：具有隱藏列值的資料框
下一篇：如何在函式中包裝一段代碼？