How to Compare Byte Value to Bytea Column in PostgreSQL 10

byteapostgresqlpostgresql-10

Is there a way to compare a single byte value to a bytea field in PostgreSQL?

For example: I have a bytea field with the value \x11AA22BB. The comparison to a byte value of AA should be true while the comparison to a byte value of 1A should be false.

The only way I see is to extract each byte from the bytea column separately with something like get_byte(string, offset) and check each one.

Best Answer

I have a bytea field with the value \x11AA22BB. The comparison to a byte value of AA should be true

It seems you're looking for the position function, knowing that it will return 0 if the byte is not contained in the string:

select position('\xAA'::bytea in '\x11AA22BB') > 0 AS contained;

 contained 
-----------
 t

If the byte value is stored in an integer, set_byte can be used to place it into a bytea:

select position(set_byte('\x00', 0, 170) in '\x11AA22BB') > 0;
Related Question