はじめに
本記事では、「CAST関数を使用しデータ型の変換に失敗した場合の動作」および「デフォルト動作の変更方法」について解説します。変換できないデータが含まれている場合、CAST関数の処理は失敗します。
CAST関数に失敗した場合は、データ型によって動作が異なります。
項番 | データ型 | CAST関数失敗時のデフォルト動作 |
|---|---|---|
① | 日付/時刻型 | NULLとなる |
② | リテラル | エラー(例:ERROR 3758)が発生する |
③ | その他のデータ型 | エラー(例:ERROR 3682)が発生する |
CAST関数については、以下の記事をご覧ください。
データ型を強制的に変更する方法
https://www.ashisuto.co.jp/cm/analytics-database/coercion_operators.html
データ型の変換の失敗例
①日付/時刻型の場合
文字データ「Vertica」と「99999」を、日付/時刻型に変換した場合の失敗例です。
## moji_suji表には、varchar型として文字と数字が格納されています。
dbadmin=> select col1 from moji_suji;
col1
---------
Vertica
99999
## 日付データへの変換が失敗しています。
dbadmin=> select CAST(col1 as date) from moji_suji;
col1
------
★NULL として扱われています。
★NULL として扱われています。
(2 rows)②リテラルの場合
リテラル「Vertica」を、数値型に変換した場合の失敗例です。
## リテラルで文字データを数値データに変換し失敗しています。
dbadmin=> select CAST('Vertica' as number);
ERROR 3758: Invalid syntax for numeric: "Vertica"③その他のデータ型の場合
文字データ「Vertica」と「99999」を、数値型に変換した場合の失敗例です。
## moji_suji表には、varchar型として文字と数字が格納されています。
dbadmin=> select col1 from moji_suji;
col1
---------
Vertica
99999
## 文字データを数値データに変換し失敗しています。
dbadmin=> select CAST(col1 as number) from moji_suji;
ERROR 3682: Invalid input syntax for numeric: "Vertica"変換可能なデータ型については、以下の記事をご覧ください。
データ型の変換と変更
https://www.ashisuto.co.jp/cm/analytics-database/changing_data_type.html
データ型の変換失敗時にデフォルト動作を変更する方法
データ型の変換に失敗した処理は、エラーが発生し処理が中断あるいはデータがNULLとして扱われます。失敗した処理をどのように扱うか、ユーザー側で制御することが可能です。データ型により制御できる動作が異なります。
項番 | データ型 | CAST関数失敗時のデフォルト動作 | 変更可能な動作 |
|---|---|---|---|
① | 日付/時刻型 | NULLとなる | エラー(ERROR 3679)を発生させる |
② | リテラル | エラー(例:ERROR 3758)が発生する | 変更不可 |
③ | その他のデータ型 | エラー(例:ERROR 3682)が発生する | NULLとして扱う |
①日付/時刻型の場合
日付データへの変換に失敗した場合は、NULLとしてデータが扱われます。EnableStrictTimeCastsパラメータを1に設定することで、日付データの変換に失敗した処理にエラー(ERROR 3679)を発生させ、処理を中断させることが可能です。
## EnableStrictTimeCastsパラメータのデフォルトを確認します。
dbadmin=> SELECT current_value from configuration_parameters WHERE parameter_name ilike '%EnableStrictTimeCasts%';
current_value
---------------
0
(1 row)
## 日付データへの変換が失敗し、NULLとして扱われます。
dbadmin=> select CAST(col1 as date) from moji_suji;
col1
------
★NULL として扱われています。
★NULL として扱われています。
(2 rows)
## EnableStrictTimeCastsパラメータを1に設定します。
dbadmin=> ALTER SESSION SET EnableStrictTimeCasts=1;
ALTER SESSION
dbadmin=> SELECT current_value from configuration_parameters WHERE parameter_name ilike '%EnableStrictTimeCasts%';
current_value
---------------
1
(1 row)
## 日付データへの変換が失敗し、ERROR 3679 が発生します。
dbadmin=> select CAST(col1 as date) from moji_suji;
ERROR 3679: Invalid input syntax for date: "99999"パラメータの設定方法については、以下の記事をご覧ください。
Verticaのパラメータ設定方法
https://www.ashisuto.co.jp/cm/analytics-database/parameters_on_vertica.html
②リテラルの場合
リテラルの場合は、デフォルトのエラーを出力させる以外に動作を変更する方法はありません。
③その他のデータ型の場合
Verticaには、変換できないデータを無視するような関数や処理は存在しませんが、変換できないデータをNULLと扱う「::！」演算子があります。「::！」演算子を使用することで、データ変換に関するエラー(ERROR 3682)を発生させずに処理を継続できます。
## CAST関数で ERROR 3682 が発生します。
dbadmin=> select CAST(col1 as number) from moji_suji;
ERROR 3682: Invalid input syntax for numeric: "Vertica"
## 「::！」演算子で変換できないデータをNULLとして扱います。
dbadmin=> select col1::!number from moji_suji;
col1
-------
★NULL として扱われています。
99999
(2 rows)注意事項
「::！」演算子を使用したNULLとして扱う動作は、テーブル・データのみ有効です。リテラルで、「::！」演算子を使用した場合は、エラーが出力されます。
## 「::！」演算子を使用した場合はエラーが出力されます。
dbadmin=> select 'Vertica' ::! FLOAT;
ERROR 3757: Invalid syntax for float: "Vertica"参考情報
Data type coercion operators (CAST)
https://docs.vertica.com/12.0.x/en/sql-reference/language-elements/operators/data-type-coercion-operators-cast/
CAST演算子を使ったデータ型の明示的な変換
https://www.ashisuto.co.jp/cm/analytics-database/coercion_operators.html
検証バージョンについて
この記事の内容はVertica 12.0で確認しています。
更新履歴
2023/6/27 本記事を公開