Reverse Byte-Order of a postgres bytea field

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.

Similar question on SO:

• Convert bigint to bytea, but swap the byte order

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

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)

Thanks to all the suggestions, I wrote this C-Language-Function that works as needed:

#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
    PG_MODULE_MAGIC;
#endif

Datum bytea_custom_reverse(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(bytea_custom_reverse);
Datum
bytea_custom_reverse(PG_FUNCTION_ARGS) {
  bytea *data = PG_GETARG_BYTEA_P_COPY(0);
  unsigned char *ptr = (unsigned char *) VARDATA(data);

  int32 dataLen = VARSIZE(data) - VARHDRSZ;

  unsigned char *start, *end;

  for ( start = ptr, end = ptr + dataLen - 1; start < end; ++start, --end ) {
    unsigned char swap = *start;
    *start = *end;
    *end = swap;
  }


  PG_RETURN_BYTEA_P(data);
}

Inspired by the answer of R. Martin, I created the pg_bswap extension that can reverse byte order for various data types, with decent speed:

https://github.com/pandrewhk/pg_bswap

Code from GitHub edited in:

#include "postgres.h"
#include "fmgr.h"
#include "utils/lsyscache.h"
#include "port/pg_bswap.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(bswap);
Datum
bswap(PG_FUNCTION_ARGS) {
    Oid     typ = get_fn_expr_argtype(fcinfo->flinfo, 0);
    int16   typlen;
    bool    typbyval;

    if (!OidIsValid(typ))
        elog(ERROR, "could not determine data type of input");

    get_typlenbyval(typ, &typlen, &typbyval);

    //pass by reference, variable-length
    if (typlen == -1){
        struct varlena *d = PG_DETOAST_DATUM_COPY(PG_GETARG_VARLENA_PP(0));
        unsigned char *ptr = (unsigned char *) VARDATA_ANY(d);
        int32 dataLen = VARSIZE_ANY_EXHDR(d);

        unsigned char *start, *end;

        for (start = ptr, end = ptr + dataLen - 1; start < end; ++start, --end) {
            unsigned char swap = *start;
            *start = *end;
            *end = swap;
        }
        PG_RETURN_POINTER(d);
    }

    //pass by reference, fixed length
    if (typlen > 0 && !typbyval) {
        unsigned char *d = (unsigned char *) PG_GETARG_POINTER(0);
        unsigned char *r = (unsigned char *) palloc(typlen);

        for (int16 i = 0; i < typlen; ++i)
            *(r+i) = *(d+typlen-i-1); //copy backwards

        PG_RETURN_POINTER(r);
    }

    //pass by value, fixed length
    if (typbyval && typlen == 1)
        PG_RETURN_DATUM(PG_GETARG_DATUM(0));

    if (typbyval && typlen == 2)
        PG_RETURN_UINT16(pg_bswap16(PG_GETARG_UINT16(0)));

    if (typbyval && typlen == 4)
        PG_RETURN_UINT32(pg_bswap32(PG_GETARG_UINT32(0)));

    if (typbyval && typlen == 8)
        PG_RETURN_INT64(pg_bswap64(PG_GETARG_INT64(0)));

    elog(ERROR, "unexpected argument type");
    PG_RETURN_NULL();
}

Was curious if there's a simple way to reverse bytea strings without dealing with hex or regex.

In case someone finds it useful, here's what I came up with:

create or replace function bytea_reverse(bytea)
returns bytea
language sql immutable parallel safe
return (
    select string_agg(substring($1 from byte_pos for 1), '')
      from generate_series(octet_length($1), 1, -1) as byte_pos);

I also had the need to output integers in little endian format that the above function can easily do with bytea_reverse(int4send(x)), but since we know it is always 4 bytes, this is about 30% faster:

create or replace function public.int4send_le(int)
returns bytea
language sql immutable parallel safe
return
(select int4send( $1 << 24
               | ($1 <<  8 & 16711680)
               | ($1 >>  8 & 65280)
               | ($1 >> 24 & 255)));

These correctly reverse both positive and negative integers as bytea:

select int4send(i), int4send_le(i), bytea_reverse(int4send(i)) from generate_series(-2,1) i;

  int4send  | int4send_le | bytea_reverse 
------------+-------------+---------------
 \xfffffffe | \xfeffffff  | \xfeffffff
 \xffffffff | \xffffffff  | \xffffffff
 \x00000000 | \x00000000  | \x00000000
 \x00000001 | \x01000000  | \x01000000

Thanks for helping this thread. And this is my choose of convert bigint to bytea in littleEndian like in C# using BitConverter.GetBytes() according on answers:

with mycte as (
select int8send(394112768534335::bigint) as conversionValue
)
SELECT decode(string_agg (
  (case when get_byte(conversionValue, x)<= 15 then ('0')  else  '' end) ||
  to_hex(get_byte(conversionValue, x))
  , ''), 'hex') AS nativeId_reverse  
   FROM mycte,  generate_series(octet_length(conversionValue) - 1, 0, -1) as x;

For search value placed in postgresql as littleEndian byteA by it Bigint presentation:

with mycte as (
select int8send(394112768534335::bigint) as conversionValue
)
Select * FROM mycte, *SomeByteaFieldTable*
where *SomeByteaId* =                                                                         
(SELECT decode(string_agg (
  (case when get_byte(conversionValue, x)<= 15 then ('0')  else  '' end) ||
  to_hex(get_byte(conversionValue, x))
  , ''), 'hex') AS nativeId_reverse  
   FROM   generate_series(octet_length(conversionValue) - 1, 1, -1) x);