How can I convert a BIT(256) into 64-byte BYTEA? More generally, how can I convert from BIT(n) to BYTEA when n is large enough that I can't cast the bits through an INT8?

Example: I've got a bunch of sha256 hashes stored in a table:

CREATE TABLE foo(sha256 CHARACTER(64) NOT NULL); -- hex encoded sha256 digests
INSERT INTO foo (sha256) VALUES 
 , ('73483f797a83373fca1b968c11785b98c4fc4803cdc7d3210811ca8b075d6d76')
 , ('b2b91baab6785f6bc6bc43c3a72f700b6bc64c478b744c539c80a857f55b00ca')
 , ('ba8cf9b1f2827f46d45b5061a65a11e93b849174185af9d92508a38b4df04945')
 , ('7507a8f88da14e9bdd5acc310ad260943bc788249a49de210205c1b537f41406')
 , ('868ca27b41d804c4877275595b4a2cf5d85559d6805693f95b68a09a69dc8634')
 , ('f10480d4b52a000b8fd8643147396b869f7c11727623023525b4ca041242356e')
 , ('53ce065eaa1e2f10eb86d10d07f8c748ce482bea9897018862f91cd142bb1eb9')
 , ('4e5d320a6c34bea34f1746bf8a85d00cf77d6d8a3b52d8649c93b2282d3f64e6')
 , ('9f1b957647c30fdd05ad319092226143e437965014235343113dc8dd6a58dda7');

  SELECT ('x' || sha256)::BIT(256) AS sha256
  FROM foo;

/* Doesn't work:
SELECT sha256::BYTEA FROM bar; -- cannot cast type bit to bytea

Notes: I got the ('x' || str)::BIT(n) trick from https://stackoverflow.com/questions/17208945/whats-the-easiest-way-to-represent-a-bytea-as-a-single-integer-in-postgresql/17214093#17214093

  • Maybe I am missing something, but the conversion to a bit is unnecessary. You can convert from hex to bytea directly: decode(sha256, 'hex') – a_horse_with_no_name Mar 23 at 20:00
  • That's correct, I'm interested in BIT(256) -> BYTEA; the hex starting-point is only for a test-case. – Steven Kalt Mar 25 at 17:28
  • Then you should have shown sample data with bit(256) – a_horse_with_no_name Mar 25 at 17:32
  • I figured hex notation would be more legible than 2560 1s and 0s. – Steven Kalt Mar 28 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.