標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです Tweet

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の3日目です。

忙しい、今年も勢いで始めた割には、事前にネタの構想があったわけでもないというギリギリの状態で書いてますがw
なんとか3つ目の窓をあけました。:)

今回は、データ型確認したいことありませんか？　という話。
Oracleを長年使い、PostgreSQL（互換含む）への移行という時に、調査していた時のこと、。。。

データ型見たいよね、これ。Oracleだとdump()で間接的にデータ型見れたなと。。。で他のエンジンではどうなのよ。。。と調べ始めたら結構大変でした。。
という２年半ぐらい前の苦労を思い出しつつ書いてみますw

調べていくと、わかりやすい関数名だったり、ありそうでないものもあるんですよね。。。。まさに、非互換の多い部分だった。。。

まず、Oracle
おなじみのdump()関数ですね。typ=nn の数字でデータ型を判断します. typ=2は、NUMBER型、typ=96は、CHAR型ですね。

SQL> select dump(123) from dual;

DUMP(123)
---------------------
Typ=2 Len=3: 194,2,24

SQL> select dump(123,8) from dual;

DUMP(123,8)
---------------------
Typ=2 Len=3: 302,2,30

SQL> select dump(123,10) from dual;

DUMP(123,10)
---------------------
Typ=2 Len=3: 194,2,24

SQL> select dump(123,16) from dual;

DUMP(123,16)
--------------------
Typ=2 Len=3: c2,2,18

SQL> select dump(123,17) from dual;

DUMP(123,17)
---------------------
Typ=2 Len=3: c2,^B,^X

SQL>
SQL> select dump('123',1016) from dual;

DUMP('123',1016)
--------------------------------------------
Typ=96 Len=3 CharacterSet=AL32UTF8: 31,32,33

次は、Postgresql

pg_typeof()って関数が使えます。結果がデータ型名で返されるのでわかりやすい！

sql=> select pg_typeof(123);
 pg_typeof
-----------
 integer
(1 row)

sql=> select pg_typeof('123'::text);
 pg_typeof
-----------
 text
(1 row)

sql=> select pg_typeof(now());
        pg_typeof
--------------------------
 timestamp with time zone
(1 row)

Athena

Prestoで使える関数なので、そのまま typeof()って関数でこれもデータ型名で返されます。
なぜ、Athenaかって？　勢いですかねぇ。

% aws athena start-query-execution --query-string "select typeof(123);" --result-configuration OutputLocation=s3://xxxx-athena-results
--------------------------------------------------------------
|                     StartQueryExecution                    |
+-------------------+----------------------------------------+
|  QueryExecutionId |  f658de1e-b711-433d-b603-15835b6e5de5  |
+-------------------+----------------------------------------+
%
% aws athena get-query-results --query-execution-id 92f46e33-0b2e-4e94-90b7-8acb3d6fce3b --output text | grep DATA
DATA	_col0
DATA	integer

Redshift

N/A

MySQL
もし、あったらツッコミよろしくお願いします。 m(_ _)m

N/A
　

参考

Oracle - DUMP
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/DUMP.html#GUID-A05793C9-B35D-4BA7-B68C-E3693BCF47A5

Oracle Built-in Data Types
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-7B72E154-677A-4342-A1EA-C74C1EA928E6

PostgreSQL - 表9.63 システムカタログ情報関数 - pg_typeof
https://www.postgresql.jp/document/11/html/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE

Athena - 6.4. Conversion Functions - typeof
https://prestodb.io/docs/0.172/functions/conversion.html

---

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2019

---

・標準はあるにはあるが癖の多いSQL 全部俺　#1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺　#2 関数名は同じでも引数が逆の罠！