LoginSignup
7

More than 5 years have passed since last update.

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

Posted at

はじめに

別件の調査で、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型全体を比較するケースってそんなにないとは思うけど。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
7