Binary Operators

Each of the functions in the following table works with BINARY and VARBINARY data types.

'='  

binary_eq

'<>'

binary_ne

'<'

binary_lt

'<='

binary_le

'>'

binary_gt

 '>='

binary_ge

'&'

binary_and

'~'

binary_not

'|'

binary_or

'#'

binary_xor

'||'

binary_cat

Notes

• If the arguments vary in length binary operators treat the values as though they are all equal in length by right-extending the smaller values with the zero byte to the full width of the column (except when using the binary_cat function). For example, given the values 'ff' and 'f', the value 'f' is treated as 'f0'.
• Operators are strict with respect to nulls. The result is null if any argument is null. For example, null <> 'a'::binary returns null.

• To apply the OR ('|') operator to a VARBINARY type, explicitly cast the arguments; for example:

  => SELECT '1'::VARBINARY | '2'::VARBINARY; 
  ?column?
  ----------
   3
  (1 row)
  

Similarly, to apply the LENGTH, REPEAT, TO_HEX, and SUBSTRING functions to a BINARY type, explicitly cast the argument; for example:

=> SELECT LENGTH('\\001\\002\\003\\004'::varbinary(4)); 
LENGTH
--------
      4
(1 row)

When applying an operator or function to a column, the operator's or function's argument type is derived from the column type.

Examples

In the following example, the zero byte is not removed from column cat1 when values are concatenated:

=> SELECT 'ab'::BINARY(3) || 'cd'::BINARY(2) AS cat1, 
'ab'::VARBINARY(3) ||
    'cd'::VARBINARY(2) AS cat2;
   cat1   | cat2
----------+------
 ab\000cd | abcd
(1 row)

When the binary value 'ab'::binary(3) is translated to varbinary, the result is equivalent to 'ab\\000'::varbinary(3); for example:

=> SELECT 'ab'::binary(3); binary
--------
 ab\000
(1 row)

The following example performs a bitwise AND operation on the two input values (see also BIT_AND):

=> SELECT '10001' & '011' as AND; 
 AND
-----
   1
(1 row)

The following example performs a bitwise OR operation on the two input values (see also BIT_OR):

=> SELECT '10001' | '011' as OR;  
  OR
-------
 10011
(1 row)

The following example concatenates the two input values:

=> SELECT '10001' || '011' as CAT;   
   CAT
----------
 10001011
(1 row)