【PostgreSQL】複数カラムからの最古日付取得について

Question

あるテーブル(例:TESTテーブル)に5つの日付が格納されるカラムが存在します。

そのカラムは以下の条件が現状確認されています。
・データ型：VARCHAR
・基本"YYYY/MM/DD"形式の日付が挿入されるが、NULLや空白が稀に挿入される。
・過去/現在/未来どの日付も挿入される可能性がある。

その5つのカラムの中で最古の日付を取得する必要があるのですが
TO_DATEで変換する場合、NULLであったカラムの日付変換時に不正な文字列となるため
NULLであった場合SysDateに置き換えるなどの処理を行うと他のカラムがすべて未来日であった場合
不整合は発生する危険性が想定されます。

MIN関数やLEAST関数うまく処理を行おうと考えたのですがなかなか思い浮かばず。
全てのカラム情報を取得し、プログラムのほうで判断することも考えましたが
できるだけSQLで解決したいのですが何か方法はありますでしょうか。
是非、ご助言をよろしくお願い致します。

Answer 1

一応動きます、最大の場合は'-infinity'とします。

create temp table hoge (id int,d1 varchar, d2 varchar,d3 varchar,d4 varchar,d5 varchar);

select id, LEAST(
    case when length(trim(coalesce(d1,'infinity')))=0 then 'infinity' else d1 end::date,
    case when length(trim(coalesce(d2,'infinity')))=0 then 'infinity' else d2 end::date,
    case when length(trim(coalesce(d3,'infinity')))=0 then 'infinity' else d3 end::date,
    case when length(trim(coalesce(d4,'infinity')))=0 then 'infinity' else d4 end::date,
    case when length(trim(coalesce(d5,'infinity')))=0 then 'infinity' else d5 end::date
    ) from hoge;

Answer 2

least()は、unnestを使用したサブクエリーで、以下のように置き換えることができます。

select (
        select item
        from unnest(array[カラム１,カラム２,カラム３]::varchar[]) as w(item)
        order by item limit 1
       )
from test
-- order by ～ limit 1 の代わりにmin()でも良い

これに、Nullや空白を除去する条件を加えると、

select (
        select item
        from unnest(array[カラム１,カラム２,カラム３]::varchar[]) as w(item)
        where trim(item)!=''
        order by item limit 1
       )
from test

となります。
型や条件の変更などはお好きなように。

Answer 3

どの程度イレギュラーなデータがあるのか提示されていませんが、まず、データクリーニングしましょう。
PostgreSQL 文字列が日付として有効かをチェック が使えそうです。
イレギュラーなデータが多過ぎて修正しきれないなら上長に相談して無効な日付は無視することも考えられます。NULLの扱いも決めておきましょう。
それが済んだら、MINとLEASTを組わせて最少の日付を取得できるでしょう。

通常のテーブル設計では無効な日付が入らないようにDATE型を使います。

Comments

[d_11]

>>通常のテーブル設計では無効な日付が入らないようにDATE型を使います。
ごもっともです。
こちらもその想定でいたのですが....過去流れで...ということらしいです。

画面から入力ということで画面側での制御により
・｢YYYY/MM/DD｣形式
・NULLまたは空白(どちらに絞っては欲しいですがどちらも無視でよい)
というところまで確認しております。
その他条件は提示されていませんがもし条件が多岐に渡るようであればご教示いただきました内容を活用し条件分岐を反映させ取得させていただきます。
ご回答ありがとうございました。