NULL な hstore には何を足しても NULL

概要

PostgreSQL には hstore という Key/Value な便利なデータ型があります。先日、hstore に格納してあるデータを更新しようとしてはまったので備忘メモです。

どういうケースではまったか

簡単のために id と hstore だけをもつテーブルを考えます。

db=# \d hstore_test
            Table "public.hstore_test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
 map    | hstore  |           |          |

このテーブルに2つの要素があるとします。

db=# select * from hstore_test ;
 id |      map
----+----------------
  1 | (null)
  2 | "apple"=>"red"
(2 rows)

id=1 の map の要素は null で、id=2 の map には "apple" => "red" という Key/Value が入っています。

いま、すべてのレコードの map に "banana" => "yellow" という Key/Value を足したくなったとします。
要するに次のような状態を期待します。

 id |                map
----+------------------------------------
  1 | "banana"=>"yellow"
  2 | "apple"=>"red", "banana"=>"yellow"

そこで、僕は次のようなクエリを書いて実行しました。map = map || hstore(...) の形のところが要素追加です。Go の append みたいな書き方ですね。

db=# UPDATE hstore_test SET map = map || hstore('banana', 'yellow');
UPDATE 2

2つの要素が更新されました。では結果を見てみましょう。

db=# select * from hstore_test ;
 id |                map
----+------------------------------------
  1 | (null)
  2 | "apple"=>"red", "banana"=>"yellow"
(2 rows)

あれ、2つ更新されているはずなのに1つにしか要素が追加されていません。

なにがまちがっているか？

これは、hstore が null のときは、何を足しても null である、ということに由来します。
すなわち、以下のようになります。

db=# SELECT null || hstore('apple', 'red') ;
 ?column?
----------
 (null)
(1 row)

なので、id=1 の map に対して上記のクエリは null な hstore に要素を足すことになって、結局 null が セットされた ということです。なので null で更新されたことになり、更新の件数に数えられました。

なるほど。ということは、hstore が null と null でないときに場合分けして2回クエリを発行すればいいのですね・・・。いやいや、PostgreSQL には COALESCE 関数という便利な関数があって、この関数は null でない最初の引数を返してくれます。

なので、

db=# UPDATE hstore_test SET map = COALESCE(map, ''::hstore) || hstore('banana', 'yellow');
UPDATE 2
db=# select * from hstore_test ;
 id |                map
----+------------------------------------
  1 | "banana"=>"yellow"
  2 | "apple"=>"red", "banana"=>"yellow"
(2 rows)

と一発で更新可能です。

まとめ

hstore が null の時に気をつけましょう・・・

( '-').oO( そして COALESCE の綴りが覚えられませんし、読めません。

Happy hacking!