Reverse Byte-Order of a postgres bytea field I'm currently working on a table that contains hashes, stored in bytea format. Converting the hashes to hex-strings however yields the wrong order of bytes. Example:

SELECT encode(hash, 'hex') FROM mytable LIMIT 1;

Output: 1a6ee4de86143e81
Expected: 813e1486dee46e1a


Is there a way to reverse the order of bytes for all entries?
Here is one method of doing it, however I would never do this. There is nothing wrong with storing bytes in a database's bytea column. But, I wouldn't bit wrangle in the database, and if I did I would use,


a C language function, or
some fancy procedural language that didn't require me exploding the inputs into a set of bytes.


This is sql-esque and should work -- here is what we're doing,


Generate a set consisting of a series of offsets 0 - (bytelength-1).
Map those offsets to bytes represented as strings of hex.
String aggregate them in reverse order.


Here is an example,

CREATE TABLE foo AS SELECT '\x813e1486dee46e1a'::bytea AS bar;

SELECT bar, string_agg(to_hex(byte), '') AS hash
FROM foo
CROSS JOIN LATERAL (
  SELECT get_byte(bar,"offset") AS byte
  FROM generate_series(0,octet_length(bar)-1) AS x("offset")
  ORDER BY "offset" DESC
) AS x
GROUP BY bar;


Two notes,


We could probably not use offset because it's reserved but you get the point.
This assumes that your hash (bar in the above) is UNIQUE.