Help us understand the problem. What is going on with this article?

postgresqlでの暗黙キャストを試す

More than 3 years have passed since last update.

この記事の話題

暗黙の型変換を定義してその挙動を確認しました。
postgresql 9.3でためしてます。

具体的な問題

テーブルを作る

create table hoge( 
  id numeric (3, 0)
  , valid numeric(1,0)
); 

insertを発行する(psqlで実施)

postgres=# insert into hoge values (1, true);
ERROR:  列"valid"は型numericですが、式は型booleanでした
行 1: insert into hoge values (1, true);
                                  ^
HINT:  式を書き換えるかキャストしなければなりません

numericに対してbooleanは指定できない。
この記事では上記のSQLがうまく動かすとことその周辺まで書きます。

暗黙の型変換を定義

暗黙の型変換を定義することで、異なる型のCASTを定義することでInsertを可能にします。
具体的には以下の手順。

1.型変換をfunctionを定義

例:integerをboolに変換する関数b2nを定義

CREATE FUNCTION b2n(bool) RETURNS numeric(1) AS
'select
  case
    when $1 IS TRUE
    then CAST(1 AS NUMERIC(1))
    else CAST(0 AS NUMERIC(1))
    end;'
LANGUAGE SQL;

2.castにfunctionを設定

integerをboolにするCASTを暗黙的に設定。(AS ASSIGNMENTは後述)

create cast(bool as numeric) WITH FUNCTION b2n(bool) AS ASSIGNMENT;

再実施

暗黙の型変換を定義して再実施するとInsertに成功する。

postgres=# insert into hoge values (1, true);
INSERT 0 1
postgres=# select * from hoge;
 id | valid
----+-------
  1 |     1
(1 行)

Insertできました、めでたい。

コンテキスト

postgresqlでの暗黙キャストには2種類コンテキストがある。
上記の例ではASSIGNMENTを設定した。

代入コンテキスト(ASSIGNMENT)

代入時に利用されるように指定する。デフォルト値(INSERTとかUPDATEとか)

任意のコンテキスト(IMPLICIT)

あらゆる文脈で呼ばれる(式とか)

コンテキストの設定による挙動の違い

コンテキストの設定による挙動の違いの例を記載します。

先ほどのboolをnumericに変換するキャストをASSIGNMENTを指定していた場合

postgres=# select 1+true;
ERROR:  演算子が存在しません: integer + boolean
行 1: select 1+true;
              ^
HINT:  指定名称、指定引数型に合う演算子がありません。明示的な型キャストが必要か
もしれません

trueに対してキャストは実施されないためこのようにエラーになります。

boolをnumericに変換するキャストをIMPLICITで指定していた場合

postgres=# select 1+true;
 ?column?
----------
        2
(1 行)

暗黙的に式の中でboolに対してcastが実施されて結果が出力されます。

暗黙キャストの削除

ここまで試したことをなかったことにします。

(1)castを削除

drop cast(bool as numeric);

(2)型変換のfunctionを削除

drop function b2n(bool);

まとめ

既存のSQLを変えられないなど困った制約に対してこういうごまかしがききそうです。

ただ、性能面の劣化などはあるので計画的に使っていきましょう。

参考

Postgresql CREATE CAST

seiketkm
私の発信内容は所属する企業を代表した発言ではありません。ほんとうです。うそじゃないです。 @seiketkm
tis
創業40年超のSIerです。
https://www.tis.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした