PostgreSQL: Extending Variable-Width Bit String Type

bit manipulationdatatypespostgresql

PostgreSQL supports Bit Strings,

SELECT B'01010101', B'01010101', '01010101'::bit(8), X'CC';
 ?column? | ?column? |   bit    | ?column? 
----------+----------+----------+----------
 01010101 | 01010101 | 01010101 | 11001100


SELECT pg_typeof(B'01010101'), pg_typeof(B'01010101'), pg_typeof('01010101'::bit(8)), pg_typeof(X'CC');
 pg_typeof | pg_typeof | pg_typeof | pg_typeof 
-----------+-----------+-----------+-----------
 bit       | bit       | bit       | bit

The type bit is typed such that I can create a function that accepts a bit of any length regardless (variable-length), and return a Bit String of variable-length:

CREATE FUNCTION type_test(_x bit)
RETURNS bit AS $$
  SELECT _x
$$ LANGUAGE sql;

SELECT type_test(X'CC'), type_test(X'CCCC');

Let's say I want to do an operation though that requires a bit-shifting algorithm, or a scratch pad bigger than the input type, how would I go about declaring that for internal use to the function. Like if I want to create a function that given X'CC' (8 bits) could use a 10 bit scratch pad.

This would almost do what I want,

CREATE FUNCTION type_test(_x bit)
RETURNS bit AS $$
  SELECT ('0' || _x || '0')::bit
$$ LANGUAGE sql;

SELECT type_test(X'55'); -- returns 0

But, it doesn't work because bit is bit(1) so the cast to it truncates the input to a single bit.

Given bit(n), how do I create a bit(n+2) to work with.

Best Answer

Using overlay

From the docs

The following SQL-standard functions work on bit strings as well as character strings: length, bit_length, octet_length, position, substring, overlay.

PostgreSQL supports overlay on Bit Strings in two forms

  • overlay(bit,bit,int)
  • overlay(bit,bit,int,int)

You can use it like this,

CREATE FUNCTION type_test(_x bit)
RETURNS bit AS $$
  SELECT overlay(B'00' PLACING _x FROM 2 FOR 0);
$$ LANGUAGE sql;
SELECT type_test(X'ff');

 type_test  
------------
 0111111110

Using "bit"

You can can also use the internal and undocumented "bit"

CREATE FUNCTION type_test(_x bit)
RETURNS bit AS $$
  SELECT ('0' || _x::text || '0')::"bit";
$$ LANGUAGE sql;

SELECT type_test(X'55');
 type_test  
------------
 0111111110
Related Question