DuckdbでSQLのCAST(型変換)がうまくいかない時どうするか

どう言った時にうまくいかないか

基本的にSQLでは列ごとの型と言うものは決まっている。
なので例えば文字と数字の混ざった列を扱いたい時にはVARCHAR(文字列)などを使用する。
この時にこの列をINTなどにCASTすると当然エラーが出る。

ただこう言った文字と数字の混ざった列から数字のみINTへCASTしたいという需要はあると思う。

解決法

Duckdbに限らず、SQLにはTRY_CASTというものがあるらしい。
型変換を試みて変換が可能なら型変換して、不可能ならNULL を返すというもの。

テーブル作成

文字列と数字の混ざった列を作成する。

import duckdb

# DuckDB接続
con = duckdb.connect(database=":memory:")

# テーブル作成
con.sql("""
CREATE TABLE sample_data (
    id INTEGER,
    value VARCHAR
);
""")

# データの挿入
data = [
    (1, '10'),       # 整数として変換可能
    (2, '20.5'),     # 整数としては変換不可能 (小数)
    (3, 'abc'),      # 整数としては変換不可能 (文字列)
    (4, '30'),       # 整数として変換可能
    (5, '100'),      # 整数として変換可能
    (6, None)        # NULL値
]

# データをテーブルに挿入
for row in data:
    con.sql("INSERT INTO sample_data (id, value) VALUES (?, ?)", params=row)

TRY_CASTを使用して変換を試みる

try_cast_result = con.sql("""
SELECT 
    id, 
    value, 
    TRY_CAST(value AS INTEGER) AS try_casted_value
FROM sample_data;
""")

print(try_cast_result)

このコードを実行すると以下のようになる。

┌───────┬─────────┬──────────────────┐
│  id   │  value  │ try_casted_value │
│ int32 │ varchar │      int32       │
├───────┼─────────┼──────────────────┤
│     1 │ 10      │               10 │
│     2 │ 20.5    │               21 │
│     3 │ abc     │             NULL │
│     4 │ 30      │               30 │
│     5 │ 100     │              100 │
│     6 │ NULL    │             NULL │
└───────┴─────────┴──────────────────┘

このようにtry_casted_valueを見るとCASTが成功したもののみINTに型変換され、他はNULLになっている。

もし困っている人がいたら試してみてください🙌小ネタでした。