PostgreSQLのデータ型で使用可能な演算子の一覧を表示する

はじめに

別件の調査で、PostgreSQLのどのデータ型で、何の演算子が使用可能なのかを調べることになった。
postgreSQL文書の「第9章 関数と演算子」では、各演算子で扱える型というのはそれぞれ書いてあるけれども、実は、どのデータ型がどの演算子を使用可能というのは、PostgreSQL文書のデータ型のところには記述がなかったりする。

なので、その方法をメモしておく。

演算子探索のクエリ例

以下のクエリでOKなはず。

SELECT 
  o.oprname, t1.typname AS lefttype, t2.typname AS righttype 
FROM pg_operator o, pg_type t1, pg_type t2 
WHERE t1.typname = 'json' AND (t1.oid = o.oprleft AND t2.oid = o.oprright)

t1.typname の条件値にデータ型名を入れればいい。上記の例ではjson型で使える演算子の一覧を表示してくれる。
これをPREPAREして型名でEXECUTEしてもいいし、なんなら型名を引数とするSQL関数にしておいてもいいくらいだ。

実行例

• integerだとヒットしない。int4と指定すべし。

test=# SELECT                   
  o.oprname, t1.typname AS lefttype, t2.typname AS righttype 
FROM pg_operator o, pg_type t1, pg_type t2 
WHERE t1.typname = 'integer' AND (t1.oid = o.oprleft AND t2.oid = o.oprright);
 oprname | lefttype | righttype 
---------+----------+-----------
(0 rows)

test=# SELECT 
  o.oprname, t1.typname AS lefttype, t2.typname AS righttype 
FROM pg_operator o, pg_type t1, pg_type t2 
WHERE t1.typname = 'int4' AND (t1.oid = o.oprleft AND t2.oid = o.oprright);
 oprname | lefttype | righttype 
---------+----------+-----------
 #       | int4     | int4
 %       | int4     | int4
 &       | int4     | int4
 *       | int4     | int8
 *       | int4     | int2
（中略）
 >=      | int4     | int4
 >>      | int4     | int4
 |       | int4     | int4
(38 rows)

• TEXT型の演算子を表示する例。

test=# SELECT 
  o.oprname, t1.typname AS lefttype, t2.typname AS righttype 
FROM pg_operator o, pg_type t1, pg_type t2 
WHERE t1.typname = 'text' AND (t1.oid = o.oprleft AND t2.oid = o.oprright);
 oprname | lefttype |  righttype  
---------+----------+-------------
 !~      | text     | text
 !~*     | text     | text
 !~~     | text     | text
 !~~*    | text     | text
 <       | text     | text
 <=      | text     | text
 <>      | text     | text
 =       | text     | text
（中略）
 ~>~     | text     | text
 ~~      | text     | text
 ~~*     | text     | text
(22 rows)

• XML型の演算子一覧を表示する例。

test=# SELECT 
  o.oprname, t1.typname AS lefttype, t2.typname AS righttype 
FROM pg_operator o, pg_type t1, pg_type t2 
WHERE t1.typname = 'xml' AND (t1.oid = o.oprleft AND t2.oid = o.oprright);
 oprname | lefttype | righttype 
---------+----------+-----------
(0 rows)

XML型で扱える演算子はないので、この結果で正しい。

• JSON型の演算子一覧を表示する例。

test=# SELECT 
  o.oprname, t1.typname AS lefttype, t2.typname AS righttype 
FROM pg_operator o, pg_type t1, pg_type t2 
WHERE t1.typname = 'json' AND (t1.oid = o.oprleft AND t2.oid = o.oprright);
 oprname | lefttype | righttype 
---------+----------+-----------
 #>      | json     | _text
 #>>     | json     | _text
 ->      | json     | int4
 ->      | json     | text
 ->>     | json     | int4
 ->>     | json     | text
(6 rows)

• JSONB型の演算子を表示する例。

test=# SELECT 
  o.oprname, t1.typname AS lefttype, t2.typname AS righttype 
FROM pg_operator o, pg_type t1, pg_type t2 
WHERE t1.typname = 'jsonb' AND (t1.oid = o.oprleft AND t2.oid = o.oprright);
 oprname | lefttype | righttype 
---------+----------+-----------
 #-      | jsonb    | _text
 #>      | jsonb    | _text
 #>>     | jsonb    | _text
 -       | jsonb    | int4
 -       | jsonb    | text
 -       | jsonb    | _text
 ->      | jsonb    | int4
 ->      | jsonb    | text
 ->>     | jsonb    | int4
 ->>     | jsonb    | text
 <       | jsonb    | jsonb
 <=      | jsonb    | jsonb
 <>      | jsonb    | jsonb
 <@      | jsonb    | jsonb
 =       | jsonb    | jsonb
 >       | jsonb    | jsonb
 >=      | jsonb    | jsonb
 ?       | jsonb    | text
 ?&      | jsonb    | _text
 ?|      | jsonb    | _text
 @>      | jsonb    | jsonb
 ||      | jsonb    | jsonb
(22 rows)

へー、JSON型では、=比較や大小比較できないのに、JSONB型ではできるのか。知らんかった。

おまけ：JSONB型同士の比較演算

ということで、どういう比較結果になるのか調べてみた。

test=# SELECT '{"a":100,"b":"010"}'::jsonb = '{"b":"010","a":100}'::jsonb;
 ?column? 
----------
 t
(1 row)

test=# SELECT '{"a":100,"b":"020"}'::jsonb < '{"b":"010","a":100}'::jsonb;
 ?column? 
----------
 f
(1 row)

どうやら、JSONB型の出力形式の文字列同士を比較しているもよう。まあ、JSONB型全体を比較するケースってそんなにないとは思うけど。