decode hex in PostgreSQL - got error "odd number of digits"

Question

I have a problem using this query:

select decode(to_hex(ascii('ل')::int),'hex')

When I execute it, I get:

ERROR: invalid hexadecimal data: odd number of digits

Answer 1

decode(..., 'hex') doesn't mean convert this hexadecimal number to something. Hex encoding is a particular encoding format for bytes, and it requires two hexadecimal digits per octet. On the other hand, to_hex converts an integer to a hexadecimal representation, and that could have an even or odd number of digits.

So the answer is, you can't do that (without some manual fixups). And it's not clear why you would want to, either. It looks like you could just do 'ل'::bytea, but that might not be what you wanted either.