Convert hex in text representation to decimal number

int

SELECT ('x' || lpad(hex, 8, '0'))::bit(32)::int AS int_val
FROM  (
   VALUES
      ('1'::text),
      ('f'),
      ('100'),
      ('7fffffff'),
      ('80000000'),     -- overflow into negative number
      ('deadbeef'),
      ('ffffffff'),
      ('00000000000000000000000000000000000000000000000008f622eefc55fed6'), -- overflow
      ('ffffffff123')  -- too long
   ) AS t(hex);

bigint

SELECT ('x' || lpad(hex, 16, '0'))::bit(64)::bigint AS int_val
FROM  (
   VALUES
      ('1'::text),
      ('8f622eefc55fed6'),
      ('00000000000000000000000000000000000000000000000008f622eefc55fed6') -- overflow
   ) AS t(hex);   

---

参考：