LoginSignup
9
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@nuko_yokohama(nuko yokohama)

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

Posted at

はじめに

長かったGWも終わってしまいましたね。かなしみ。
今日は細かすぎて伝わらないPostgreSQLの小ネタを書きます。

検証環境

今回は、PostgreSQL 12-develを用いて確認しました。たぶん、PostgreSQL 11などでも同様の挙動になるとは思いますが・・・。

発端 - VALUESコマンドの挙動

PostgreSQLにはVALUESコマンドというのがあります。これは「行セットを計算する」とPostgreSQL文書上は書かれていますが、雑に書くと、INSERT時のVALUE句相当だけを切り出して、それを結果として返却できるというものです。
実行例を見たほうがピンときやすいかもしれません。


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

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

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

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

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()を使って確認してみます。

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したときも同様です。

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および関連する構文にもさらっと書いてありますが、具体的に詳しくは書いてない・・・。
なので、実際に動かして確認してみました。今回のターゲットは、数値型、文字型、日付型の3種類。確認に使ったPostgreSQLバージョンは12-develですが、たぶん他のバージョンでもそんなに差はないと思います。

数値データ型

数値型の調査対象はPostgreSQL文書の数値データ型の表にあるものを対象としました。

  • smallint
  • integer
  • bigint
  • numeric
  • real
  • double precision

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

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

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

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

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に型変換されます。

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文書の文字型の表にあるものを対象としました。

  • varchar(n)
  • char(n)
  • text

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

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

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を設定した場合。

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)を設定した場合。

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文書の日付/時刻データ型の表にあるもののうち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に合わせられます。

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があった場合には、型変換できないらしくエラーとなります。

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間も同様にエラーとなります。

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に型変換されます(記述を逆にしても同様)。

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に型変換されます(記述を逆にしても同様)。

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することはそんなにないかもしれませんが、数値データ型, 文字型, 日付/時刻データ型で少しずつ挙動が異なるのが確認できたのは興味深かったです。

9
8
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?