目次
はじめに
本記事では、「CAST関数を使用しデータ型の変換に失敗した場合の動作」および「デフォルト動作の変更方法」について解説します。変換できないデータが含まれている場合、CAST関数の処理は失敗します。CAST関数に失敗した場合は、データ型によって動作が異なります。
| 項番 | データ型 | CAST関数失敗時のデフォルト動作 |
|---|---|---|
| ① | 日付/時刻型 | NULLとなる |
| ② | リテラル | エラー(例:ERROR 3758)が発生する |
| ③ | その他のデータ型 | エラー(例:ERROR 3682)が発生する |
CAST関数については、以下の記事をご覧ください。
データ型を強制的に変更する方法
http://vertica-tech.ashisuto.co.jp/coercion_operators/
データ型の変換の失敗例
①日付/時刻型の場合
文字データ「Vertica」と「99999」を、日付/時刻型に変換した場合の失敗例です。|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
## 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」を、数値型に変換した場合の失敗例です。|
1 2 3 4 |
## リテラルで文字データを数値データに変換し失敗しています。 dbadmin=> select CAST('Vertica' as number); ERROR 3758: Invalid syntax for numeric: "Vertica" |
③その他のデータ型の場合
文字データ「Vertica」と「99999」を、数値型に変換した場合の失敗例です。|
1 2 3 4 5 6 7 8 9 10 11 12 |
## 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" |
変換可能なデータ型については、以下の記事をご覧ください。
データ型の変換と変更
http://vertica-tech.ashisuto.co.jp/changing_data_type/
データ型の変換失敗時にデフォルト動作を変更する方法
データ型の変換に失敗した処理は、エラーが発生し処理が中断あるいはデータがNULLとして扱われます。失敗した処理をどのように扱うか、ユーザー側で制御することが可能です。データ型により制御できる動作が異なります。| 項番 | データ型 | CAST関数失敗時のデフォルト動作 | 変更可能な動作 |
|---|---|---|---|
| ① | 日付/時刻型 | NULLとなる | エラー(ERROR 3679)を発生させる |
| ② | リテラル | エラー(例:ERROR 3758)が発生する | 変更不可 |
| ③ | その他のデータ型 | エラー(例:ERROR 3682)が発生する | NULLとして扱う |
①日付/時刻型の場合
日付データへの変換に失敗した場合は、NULLとしてデータが扱われます。EnableStrictTimeCastsパラメータを1に設定することで、日付データの変換に失敗した処理にエラー(ERROR 3679)を発生させ、処理を中断させることが可能です。|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
## 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のパラメータ設定方法
http://vertica-tech.ashisuto.co.jp/parameters_on_vertica/
②リテラルの場合
リテラルの場合は、デフォルトのエラーを出力させる以外に動作を変更する方法はありません。③その他のデータ型の場合
Verticaには、変換できないデータを無視するような関数や処理は存在しませんが、変換できないデータをNULLと扱う「::！」演算子があります。「::！」演算子を使用することで、データ変換に関するエラー(ERROR 3682)を発生させずに処理を継続できます。|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
## 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として扱う動作は、テーブル・データのみ有効です。リテラルで、「::！」演算子を使用した場合は、エラーが出力されます。|
1 2 3 4 |
## 「::！」演算子を使用した場合はエラーが出力されます。 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 failures
https://docs.vertica.com/12.0.x/en/sql-reference/language-elements/operators/data-type-coercion-operators-cast/cast-failures/
CAST演算子を使ったデータ型の明示的な変換
http://vertica-tech.ashisuto.co.jp/coercion_operators/
