【Postgresql】【Redshift】SELECTの取得値にNULLを定数で指定するときは型指定する

やらかした。

やらかし

SELECT 
   order.orderno,
   orderdtl.orderidx,
   order.orderdate,
   NULL AS customer_id
FROM
   order
;

上記のように、SELECTで型ヒントなしに NULL を指定すると、型が "unknown" となる。

CREATE VIEW view_orderlist AS SELECT 
   order.orderno::numeric,
   orderdtl.orderidx::int,
   order.orderdate::date,
   NULL::unknown AS customer_id
FROM
   order
;

通常の選択操作では困らないが、特定のSQLでエラーとなる。

SELECT COUNT(DISTINCT customer_id) FROM view_orderlist;
-- postgresql 9.4 :  ERROR:  failed to find conversion function from unknown to text
-- Redshift：[Amazon](500310) Invalid operation: could not identify an equality operator for type "unknown";

エラー内容を読むに、unknown 型から別の型へ変換しようとしたが、暗黙の変換方法が見つからなかった旨のエラーが出力される。

これは、型推論ができない場合の事象らしく、 CASE や coalesce() などで具体的な型になる値が与えられているなら問題がない。

ひとまずの解消法

ビューの定義や列の選択時に型を指定することで解消する。

CREATE VIEW view_orderlist AS SELECT 
   order.orderno,
   orderdtl.orderidx,
   order.orderdate,
   NULL::numeric(14) AS customer_id
FROM
   order
;

何故わかったのか？

手製のアプリケーションでも count(distinct 列) なんて書かないから大丈夫だろうと思いきや Tableauという製品がなにやらデータ取得前に上記相当のSQLを実行するらしく、この項目を使うと画面がバグって使えなくなってしまった。

超悔しい。