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.

StackExchange Database Administrators Q#156700, answer score: 6