Search code examples
castingamazon-redshiftbit

Redshift casting issue to bit

I want to cast a hex string to bit on redshift as follow: select 'x827ccb0eea8a706c'::bit(64) -> This statement works well.

The problem is that I retrieve the 'x827ccb0eea8a706c' value as a varchar/text. If the value is casted as a string, I can't convert it to bit: select 'x827ccb0eea8a706c'::text::bit(64) -> Doesn't work!

How can I solve this issue? How can I cast text to bit?

Solution

  • Two issues here:

    1. The binary type in Redshift is called BINARY or VARBYTE.
    2. A pre-existing function for the conversion of hex text to binary exists in Redshift called FROM_HEX()

    The intended query can be written as:

    SELECT FROM_HEX(substr(md5('12345'),1,16))::BIGINT;