PostgreSQLで型の異なる列をUNIONしたときの挙動

#はじめに
長かったGWも終わってしまいましたね。かなしみ。
今日は細かすぎて伝わらないPostgreSQLの小ネタを書きます。
## 検証環境
今回は、PostgreSQL 12-develを用いて確認しました。たぶん、PostgreSQL 11などでも同様の挙動になるとは思いますが・・・。

#発端 - VALUESコマンドの挙動
PostgreSQLには[VALUESコマンド](https://www.postgresql.jp/document/11/html/sql-values.html)というのがあります。これは「行セットを計算する」とPostgreSQL文書上は書かれていますが、雑に書くと、INSERT時のVALUE句相当だけを切り出して、それを結果として返却できるというものです。
実行例を見たほうがピンときやすいかもしれません。

```sql

postgres=# VALUES (1, '東京都', '港区'),(2, '神奈川県', '横浜市'),(3, '神奈川県', '町田市');
 column1 | column2  | column3
---------+----------+---------
       1 | 東京都   | 港区
       2 | 神奈川県 | 横浜市
       3 | 神奈川県 | 町田市
(3 rows)
```

``VALUES``に記述した各列はFROM句と組み合わせると別名を付与することもできます。

```sql
postgres=# SELECT * FROM
(VALUES
  (1, '東京都', '港区'),
  (2, '神奈川県', '横浜市'),
  (3, '神奈川県', '町田市')
) AS t (id, pref, city);
 id |   pref   |  city
----+----------+--------
  1 | 東京都   | 港区
  2 | 神奈川県 | 横浜市
  3 | 神奈川県 | 町田市
(3 rows)
```

で、各列は``expression``なので、個々に型変換の指定も可能です。

```sql
postgres=# SELECT * FROM
(VALUES
  (1::integer, '東京都'::char(3), '港区'),
  (2::numeric, '神奈川県'::varchar(4), '横浜市'),
  (3::float, '神奈川県'::text, '町田市')
) AS t (id, pref, city);
 id |   pref   |  city
----+----------+--------
  1 | 東京都   | 港区
  2 | 神奈川県 | 横浜市
  3 | 神奈川県 | 町田市
(3 rows)
```
実行結果はこのようになりますが、さて、id列とpref列のデータ型はどうなっているのか。
PostgreSQLの``pg_typeof()``を使って確認してみます。

```sql
postgres=# SELECT id, pg_typeof(id), pref, pg_typeof(pref) FROM
(VALUES
  (1::integer, '東京都'::char(3), '港区'),
  (2::numeric, '神奈川県'::varchar(4), '横浜市'),
  (3::float, '神奈川県'::text, '町田市')
) AS t (id, pref, city);
 id |    pg_typeof     |   pref   | pg_typeof
----+------------------+----------+-----------
  1 | double precision | 東京都   | character
  2 | double precision | 神奈川県 | character
  3 | double precision | 神奈川県 | character
(3 rows)
```
この場合、``id``列は浮動小数点型の``double precision``、``pref``列は``charcter``型として扱われます。
何らかの規則によって、暗黙の型変換が行われているのですが、どういう規則なのか、ちょっと気になりますよね。

## UNIONでも同じ
今までの例は``VALUES``コマンドでの例なのですが、実はこれは複数のSELECTコマンドをUNIONしたときも同様です。

```sql
testdb=# SELECT n, pg_typeof(n), t, pg_typeof(t) FROM (
  SELECT 1::integer n, 'abc' t
  UNION
  SELECT 2::numeric, 'defg'::char(8)
  UNION
  SELECT 3::float, 'hijkl'::varchar(8)
) t;
 n |    pg_typeof     |    t     | pg_typeof
---+------------------+----------+-----------
 1 | double precision | abc      | character
 3 | double precision | hijkl    | character
 2 | double precision | defg     | character
(3 rows)
```
``VALUES``コマンドは普段使わなくても、UNIONを使う機会はあると思うので、この暗黙の型変換については知っておいても損はなさそうです。

# UNIONにおける暗黙の型変換
UNIONにおける型変換に関しては、PostgreSQL文書の[UNION、CASEおよび関連する構文](https://www.postgresql.jp/document/11/html/typeconv-union-case.html)にもさらっと書いてありますが、具体的に詳しくは書いてない・・・。
なので、実際に動かして確認してみました。今回のターゲットは、数値型、文字型、日付型の3種類。確認に使ったPostgreSQLバージョンは12-develですが、たぶん他のバージョンでもそんなに差はないと思います。

## 数値データ型
数値型の調査対象はPostgreSQL文書の[数値データ型](https://www.postgresql.jp/document/11/html/datatype-numeric.html#DATATYPE-NUMERIC-TABLE)の表にあるものを対象としました。

* smallint
* integer
* bigint
* numeric
* real
* double precision

``serial*`` シリーズや、``decimal``のようなエイリアスは割愛。

検証した結果、型の強さは以下のようになっていました。

**(よわい) smallint, integer, bigint, numeric, real, double precision (つよい)**

例えばbigint型の列とnumericの列がUNION結果に混在していた場合、bigint型の列はnumeric型に型変換されます。

```sql
testdb=# SELECT n, pg_typeof(n) FROM (
  SELECT 1::bigint as n
  UNION
  SELECT 2::numeric as n
) t;
 n | pg_typeof
---+-----------
 1 | numeric
 2 | numeric
(2 rows)
```

同様にnumericとreal(float)が混在していた場合には、floatに型変換されます。

```sql
testdb=# SELECT n, pg_typeof(n) FROM (
  SELECT 1::real as n
  UNION
  SELECT 2::numeric as n
) t;
 n | pg_typeof
---+-----------
 1 | real
 2 | real
(2 rows)
```

ここは注意が必要で、正確な値を保持するためにnumericを使っていても、realに型変換されるので正確な値が保持されなくなってしまいます。

## 文字型
文字型の調査対象はPostgreSQL文書の[文字型](https://www.postgresql.jp/document/11/html/datatype-character.html#DATATYPE-CHARACTER-TABLE)の表にあるものを対象としました。

* varchar(n)
* char(n)
* text

検証した結果、想定外の結果が・・・

最初のクエリにchar(n)（bpchar)を設定した場合。

```sql
testdb=# SELECT t, pg_typeof(t), pg_typeof(t)::int  FROM (
  SELECT 'abc'::char(8) t
  UNION
  SELECT 'defg'::text
) t;
    t     | pg_typeof | pg_typeof
----------+-----------+-----------
 abc      | character |      1042
 defg     | character |      1042
(2 rows)
```

最初のクエリにtextを設定した場合。

```sql
testdb=# SELECT t, pg_typeof(t), pg_typeof(t)::int  FROM (
  SELECT 'abc'::text t
  UNION
  SELECT 'defg'::char(8)
) t;
  t   | pg_typeof | pg_typeof
------+-----------+-----------
 abc  | text      |        25
 defg | text      |        25
(2 rows)
```

最初のクエリにvarchar(n)を設定した場合。

```sql
testdb=# SELECT t, pg_typeof(t), pg_typeof(t)::int  FROM (
  SELECT 'abc'::varchar(8) t
  UNION
  SELECT 'defg'::char(8)
) t;date
  t   |     pg_typeof     | pg_typeof
------+-------------------+-----------
 abc  | character varying |      1043
 defg | character varying |      1043
(2 rows)
```

なんということでしょう。
**文字型の場合には、最初に処理されるSELECTに記述されたデータ型に合わせてしまう**ようです。
UNION前後の式の順序によって型が変わってしまうのはちょっと罠っぽいですね。

## 日付/時刻データ型
日付/時刻データ型の調査対象はPostgreSQL文書の[日付/時刻データ型](https://www.postgresql.jp/document/11/html/datatype-datetime.html#DATATYPE-DATETIME-TABLE)の表にあるもののうちinterval以外の型を対象としました。

* timestamp
* timestamp with time zone
* date
* time
* time with time zone

検証結果を雑にまとめると以下のようになります。

* **(よわい) date, timestamp (つよい)**
* **(よわい) without time zone , with time zone (つよい)**
* timeとtimestamp/dateが混在する場合はエラー

### timestamp/data/time

dateとtimestampがあった場合には、timestampに合わせられます。

```sql
testdb=# SELECT t, pg_typeof(t), pg_typeof(t)::int  FROM (
  SELECT '2019-05-07'::date t
  UNION
  SELECT '2019-05-08'::timestamp t
) t;
          t          |          pg_typeof          | pg_typeof
---------------------+-----------------------------+-----------
 2019-05-07 00:00:00 | timestamp without time zone |      1114
 2019-05-08 00:00:00 | timestamp without time zone |      1114
(2 rows)

testdb=#
```

timeとtimestampがあった場合には、型変換できないらしくエラーとなります。

```sql
testdb=# SELECT t, pg_typeof(t), pg_typeof(t)::int  FROM (
  SELECT '20:30:00'::time t
  UNION
  SELECT '2019-05-08'::timestamp t
) t;
psql: ERROR:  UNION could not convert type timestamp without time zone to time without time zone
LINE 4:   SELECT '2019-05-08'::timestamp t
                 ^
```

timeとdate間も同様にエラーとなります。

```sql
testdb=# SELECT t, pg_typeof(t), pg_typeof(t)::int  FROM (
  SELECT '20:30:00'::time t
  UNION
  SELECT '2019-05-08'::date t
) t;
psql: ERROR:  UNION could not convert type date to time without time zone
LINE 4:   SELECT '2019-05-08'::date t
                 ^
```

### timezoneの有無
では、timestampおよびtimeのtime zoneが混在していた場合はどうなるのか、これも確認してみました。

timestamp with time zone/timestamp without timezoneの場合は、timestamp with time zoneに型変換されます(記述を逆にしても同様)。

```sql
testdb=# SELECT t, pg_typeof(t), pg_typeof(t)::int  FROM (
  SELECT '2019-05-07 20:30:00'::timestamp without time zone t
  UNION
  SELECT '2019-05-08 20:30:00'::timestamp with time zone t
) t;
           t            |        pg_typeof         | pg_typeof
------------------------+--------------------------+-----------
 2019-05-07 20:30:00+00 | timestamp with time zone |      1184
 2019-05-08 20:30:00+00 | timestamp with time zone |      1184
(2 rows)
```

time with time zone/time without time zoneの場合も同様に、time with time zoneに型変換されます(記述を逆にしても同様)。

```sql
testdb=# SELECT t, pg_typeof(t), pg_typeof(t)::int  FROM (
  SELECT '20:30:00'::time without time zone t
  UNION
  SELECT '21:30:00'::time with time zone t
) t;
      t      |      pg_typeof      | pg_typeof
-------------+---------------------+-----------
 20:30:00+00 | time with time zone |      1266
 21:30:00+00 | time with time zone |      1266
(2 rows)
```

# おわりに
現実に、型の異なる結果をUNIONすることはそんなにないかもしれませんが、数値データ型, 文字型, 日付/時刻データ型で少しずつ挙動が異なるのが確認できたのは興味深かったです。