Reverse Byte-Order of a postgres bytea field

If you need just to reverse bytes in the bytea value there is the (relatively) simple and fast solution using plpythonu:

create or replace function reverse_bytea(p_inp bytea) returns bytea stable language plpythonu as $$
  b = bytearray()
  b.extend(p_inp)
  b.reverse()
  return b
$$;

select encode(reverse_bytea('\x1a6ee4de86143e81'), 'hex');
----
813e1486dee46e1a

However I suppose that something wrong with data itself (the storage way, the data interpretation...)

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,

1. Generate a set consisting of a series of offsets 0 - (bytelength-1).
2. Map those offsets to bytes represented as strings of hex.
3. 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(get_byte(bar,x.offset)), '')
FROM foo
CROSS JOIN LATERAL (
  SELECT * FROM generate_series(0,octet_length(bar)-1) AS x("offset")
  ORDER BY "offset" DESC
) AS x
GROUP BY bar;

Two notes,

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

You could treat encoded representation as text and use regexp to reverse byte by byte.

SELECT string_agg(reverse(b[1]),'')
FROM regexp_matches(reverse(encode('STUFF','hex')),'..','g')b;

Another (more verbose) method:

WITH bytes AS (
  SELECT row_number() over() AS n, byte[1]
  FROM regexp_matches( encode( 'STUFF', 'hex' ), '..', 'g' ) AS byte
), revbytes AS (
  SELECT * FROM bytes ORDER BY n DESC
)
SELECT array_to_string(array_agg(byte),'')
FROM revbytes;

Sample usage:

(filip@[local:/var/run/postgresql]:5432) filip=# SELECT encode( 'STUFF', 'hex' );
   encode   
------------
 5354554646
(1 row)

(filip@[local:/var/run/postgresql]:5432) filip=# SELECT string_agg(reverse(b[1]),'')FROM regexp_matches(reverse(encode('STUFF','hex')),'..','g')b;
 string_agg 
------------
 4646555453
(1 row)