0

could you please help me to convert this expression in the PostgreSQL syntax to the Redshift syntax:

(('x'::text || lpad(md5('0036f392-c2bc-46d5-b413-cd7772bcd4a1'), 16, '0'::text)))::bit(64)::bigint

The result equals -5735530232431975337 in PostgreSQL. Redshift throws an error: "cannot cast type text to bit".

UPD It seems like similar question was asked here Hex string to integer conversion in Amazon Redshift

Margarita
  • 78
  • 1
  • 7

1 Answers1

1

The strtol() function takes a string representation of a number in any base and converts it to a bigint. https://docs.aws.amazon.com/redshift/latest/dg/r_STRTOL.html

I've used this to change md5() results into integers for summing and comparison. I wrote this up in another answer here: More efficient way of comparing two tables in Redshift?

Bill Weiner
  • 8,835
  • 2
  • 7
  • 18
  • Thanks, so basically `select strtol((lpad(md5('UUID string'), 16, '0')), 16)` does the same for some UUIDs. For example, for this value `0010f3c8-cbf7-49c7-986e-9716085448d5`. But for those UUIDs for which the Postgre expression returns a negative value, like `0061d649-1f3b-4478-bfea-09a1c6012bc0` redshift returns `9223372036854775807`, which seems to be the biggest integer value. Do you have an idea what could be wrong? – Margarita Dec 12 '22 at 18:55
  • I expect so. the md5() of the second string is fb85eb3458d660193e238cbd55628e97 which has a left most bit of 1 which in binary indicates a negative number. However strtol() makes no such assumption. You could create a CASE statement based on the first bit and do the right math but I expect there is another issue. md5() returns a 128 bit value but BIGINT only hold a 64 bit value. How are you preventing overflow? I used a substring() to select a portion of the md5 value. – Bill Weiner Dec 12 '22 at 22:22
  • `lpad` will return a substring: "..if longer than the remaining length or specified length it will be truncated on the right." – Margarita Dec 13 '22 at 13:55
  • Gotcha. So one way to avoid the the signed vs unsigned bigint difference would be to truncate to 15 characters. If the first bit isn't a 1 then both interpretations will be positive. – Bill Weiner Dec 13 '22 at 15:39