Reverse Byte-Order of a postgres bytea field

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.

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...)

Solutions with tools in vanilla Postgres:

I added a column bytea_reverse to both solutions. Remove it if you don't need it.

With get_byte():

SELECT t.b, text_reverse, decode(text_reverse, 'hex') AS bytea_reverse
FROM   tbl t
LEFT   JOIN LATERAL (
   SELECT string_agg(to_hex(get_byte(b, x)), '') AS text_reverse
   FROM   generate_series(octet_length(t.b) - 1, 0, -1) x
   ) x ON true;

This is similar to what @Evan provided. Most of his excellent explanation applies. But:

Use LEFT JOIN LATERAL ... ON true or you lose rows with NULL values.
generate_series() can provide numbers in reverse, so we do not need another ORDER BY step.
While using a LATERAL join, aggregate in the subquery. Less error prone, easier to integrate with more complex queries, and no need to GROUP BY in the outer query.

With regexp_matches():

SELECT t.b, text_reverse, decode(text_reverse, 'hex') AS bytea_reverse
FROM   tbl t
LEFT   JOIN LATERAL (
   SELECT string_agg(byte[1], '' ORDER  BY ord DESC) AS text_reverse
   FROM   regexp_matches(encode(t.b, 'hex' ), '..', 'g' ) WITH ORDINALITY AS x(byte, ord)
   ) x ON true;

This is similar to the "verbose" variant @filiprem provided. But:

Use LEFT JOIN LATERAL ... ON true or you lose rows with NULL values.
Use WITH ORDINALITY to get row numbers "for free". So we neither need another subquery with row_number() nor a double reverse(). Details:
- PostgreSQL unnest() with element number
Reverse ordering can be done in the aggregate function. (But it might be a bit faster to order in the subquery and add another subquery layer to aggregate pre-ordered rows.)
One subquery (or two) instead of two CTE is typically faster.

Reverse Byte-Order of a postgres bytea field

Tags:

Postgresql

Psql

Bytea

Related

Recent Posts