PostgreSQLで実行エラーが発生する

Question

ご覧いただきありがとうございます。

以下の簡単なSQLが動きません。
お知恵を拝借したくよろしくお願いいたします。

select null
union all
select null
union all
select 0
;

（Messages
ERROR: UNION types text and integer cannot be matched
LINE 5: select 0
^
SQL state: 42804
Character: 52）

また、以下のSQLは動きます。

select null
union all
select 0
union all
select null
;

**nullを2つ先に繋げるとtext型に確定される?**のでしょうか。
Documentがあれば参照URLだけでも記載頂けると大変助かります。

諸事情で結合順序は変えたくないのです。

バージョンは10.1です。

皆様どうぞよろしくお願いいたします。

Answer 1

select ～ union の場合、unionされるカラムの属性は最初に決定された属性が使用されます。

null自体の型はunknown(不明)です。
但し、union するということは取り敢えず型を決める必要があり、
unknownでない型が採用されますが、unknownしか無い時はtext型が採用されます。
一方で、単に数値だけを記述するとその数値の大きさに合わせた数値型が暗黙で使用されます。

unionの場合型は一致しなければなりませんから、質問のSQLではtextとintegerで型が不一致となりエラーとなります。

select null
union all
select null
--↑ text型が採用される

select null
union all
select 0
--↑ integer型が採用される

select null
union all
select null
union all
select 0
--↑ text型が採用された後に、integer型が現れるのでエラーになる

通常最初のselectで型が明示される記述としていれば、問題ありません。

select null::int
union all
select null
union all
select 0

諸事情で結合順序は変えたくないのです。

順序はorder byによってのみ保証されます。
単に記述した順序で順番が意図通りになっていたとしてもそれは保証されているものではありません。
union all という記述があるので、その順番に展開されているに過ぎません。

Comments

[yado]

これです！
スッキリしました、ありがとうございました。

[退会済みユーザー]

9.6のドキュメントで根拠っぽいのは
https://www.postgresql.jp/document/9.6/html/typeconv-union-case.html
でしょうかね。unknown型に言及ありますし。

[yado]

これに従ってそうな挙動ですね。
ありがとうございます。

[sazi]

>m6u さん
フォローありがとうございます。

Answer 2

最初のNULLをCASTでデータ型を明示してあげては？ 型キャスト

Comments

[yado]

なるほど、nullをCASTするのですね。
0のCASTばかり考えてました。
nullの型確定のタイミングについてはどのようにお考えでしょうか？
お手数ですがお時間ありましたらお願いいたします。

[Orlofsky]

NULLはデータ型を持っていないから、
CAST(NULL AS INTEGER)
などでデータ型を指定すれば良いだけでは？

[yado]

確かに目的のことを行うのはそれで達成されるのですが、
何故始めのnullで型確定が行われないのかなと思いまして、、、
nullはデータ型を持っていないから、
1つ目ではなく２つ目で型確定が行われるというのは私には納得がいかないのです。
しかし、そこを突き詰めても仕方がありませんね。
今回はnullをCASTすることで対応したいと思います。
大変助かりました。ありがとうございました。

[Orlofsky]

憶測ですが、最初のUNION ALLでNULLをつなげたが、次のUNION ALLで数値が出てきて、データ型が不明のNULLと数値とのUNIONはダメ、って怒られているのでは？
最初から
SELECT COALESCE(NULL, 0)
にすれば良いかも？

[yado]

select null
union all
select 0
↑はうまくいくので、UNION ALLを行う際に型確定する仕様なんでしょうかね。
（断定は危険ですけど）

NULL
NULL
0
と出力したいので、COALESCE(NULL, 0) をNULLに適用出来ません。
（0は意味がありません）

[yado]

上の方にご回答頂いた内容で解決しました。
質問の仕方が悪く、申し訳ございませんでした。。。