Sample Header Ad - 728x90

How to convert bit(n) to bytea when n >= 256?

1 vote
1 answer
1042 views
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 
   ('e643a277fab67cca43d9348623c7551d7382784033a697eb7f5484178be7c53d')
 , ('73483f797a83373fca1b968c11785b98c4fc4803cdc7d3210811ca8b075d6d76')
 , ('b2b91baab6785f6bc6bc43c3a72f700b6bc64c478b744c539c80a857f55b00ca')
 , ('ba8cf9b1f2827f46d45b5061a65a11e93b849174185af9d92508a38b4df04945')
 , ('7507a8f88da14e9bdd5acc310ad260943bc788249a49de210205c1b537f41406')
 , ('868ca27b41d804c4877275595b4a2cf5d85559d6805693f95b68a09a69dc8634')
 , ('f10480d4b52a000b8fd8643147396b869f7c11727623023525b4ca041242356e')
 , ('53ce065eaa1e2f10eb86d10d07f8c748ce482bea9897018862f91cd142bb1eb9')
 , ('4e5d320a6c34bea34f1746bf8a85d00cf77d6d8a3b52d8649c93b2282d3f64e6')
 , ('9f1b957647c30fdd05ad319092226143e437965014235343113dc8dd6a58dda7');

CREATE TABLE bar AS
  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
Asked by Steven Kalt (73 rep)
Feb 9, 2021, 02:17 AM
Last activity: Aug 12, 2024, 10:04 AM