PostgreSQL におけるbit演算とbit型<=>bytea型の変換

はじめに

OracleではRAW型というものがあり、UTL_RAWパッケージを使ってRAW型のbit演算やbyte単位でのアクセスが簡単にできました。
ただ、Postgresqlで同じ事をやろうとすると、途端に難しくなったのでメモとしてここに記載します。

環境

PostgreSQL 9.6.8

bit記法

b'' で0,1を囲むと、bit列として認識されます。

=> select b'11110000' as bit_a;
  bit_a
----------
 11110000
(1 row)

=> select pg_typeof(b'11110000');
 pg_typeof
-----------
 bit
(1 row)

bit演算

bit型同士であれば、& | でand,or演算ができます。

=> select b'11110000' & b'10101111' as and;
   and
----------
 10100000
(1 row)

=> select b'11110000' | b'10101111' as or;
    or
----------
 11111111
(1 row)

bytea型とは

byte単位でアクセスできるbyte列と考えてもらって問題ないでしょう(汗)
bit型でデータを保持してると、バイト単位でアクセスができず不便なので、oracleでraw型を使ってた人はbytea型でデータを保持する事になると思います。
また、bytea型同士のbit演算はできないので、演算するときは一旦bit型に変換する必要があります。

bytea記法

いろいろ書き方はあるみたいですが、16進数表記を E'\x' で囲むのが一番分かりやすいです。get_byteなどのバイナリ文字列関数を使って、バイト単位でアクセスが可能です。

=> select E'\\x0f0e1051'::bytea;
   bytea
------------
 \x0f0e1051
(1 row)

# 0 byte目にアクセス
=> select get_byte(E'\\x0f0e1051'::bytea, 0);
 get_byte
----------
       15
(1 row)

bit型からbytea型への変換

8byteを超えるビット列だと、bigintへのキャストでエラーになってしまうので、1byteずつ変換する必要があります。

# 1byte単位
=> select decode(lpad(to_hex(b'00001111'::int), 2, '0'), 'hex');
 decode
--------
 \x0f
(1 row)

=> select decode(lpad(to_hex(b'11110000'::int), 2, '0'), 'hex');
 decode
--------
 \xf0
(1 row)

# 3byte単位
=> select decode(lpad(to_hex(b'111100001111111101010000'::int), 6, '0'), 'hex');
  decode
----------
 \xf0ff50
(1 row)

bytea型からbit型への変換

# 2バイト分の変換
=> select ('x' || ltrim(E'\\x0f0c'::bytea::text, '\\x'))::bit(16);
       bit
------------------
 0000111100001100
(1 row)

まとめ

Postgresqlはbit型は用意しているけど、sqlクエリでbit演算をする事を想定していないのかもしれない。
100byte同士のbit演算をsqlクエリや、function内でできるようにしてほしい。
現状は1バイトずつ bit <=> bytea 変換の手間が発生してしまう。