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?

  • 3
    Why do you expect the hash to be reversed byte-wise? That's ... weird. – Craig Ringer Nov 30 '16 at 8:09

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(byte), '') AS hash
FROM foo
  SELECT get_byte(bar,"offset") AS byte
  FROM generate_series(0,octet_length(bar)-1) AS x("offset")
  ORDER BY "offset" DESC
) AS x

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.
| improve this answer | |

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 array_to_string(array_agg(byte),'')
FROM revbytes;

Sample usage:

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

(filip@[local:/var/run/postgresql]:5432) filip=# SELECT string_agg(reverse(b[1]),'')FROM regexp_matches(reverse(encode('STUFF','hex')),'..','g')b;
(1 row)
| improve this answer | |
  • Nice catch. I thought about regexp_matches and reverse but not guessed about double-reverse :) – Abelisto Nov 29 '16 at 23:08

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()
  return b

select encode(reverse_bytea('\x1a6ee4de86143e81'), 'hex');

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

| improve this answer | |
  • I think you should have went the extra distance if you're going to use plpython: you should have returned the hex-encoded string. – Evan Carroll Nov 30 '16 at 5:06
  • 1
    @EvanCarroll And convert the result back to bytea if somebody needs just reverse the value? IMO one function - one task. However it's a matter of taste. – Abelisto Nov 30 '16 at 8:23

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
   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
   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:
  • 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:

| improve this answer | |
  • The main disadvantage is that the SQL/PLSQL is much slower then the other scripting languages like Python/Perl/TCL/Java for the scalar code which does not acts with data in the DB. At this point of view the using of the "C language function" as suggested by @Evan is the best solution but it is somewhere complicated for the distribution. – Abelisto Dec 3 '16 at 2:13
  • 2
    @Abelisto: Yup, a C function should be faster by orders of magnitude - especially for bytea values longer than a couple of bytes. – Erwin Brandstetter Dec 3 '16 at 2:21

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

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


Datum bytea_custom_reverse(PG_FUNCTION_ARGS);

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;

| improve this answer | |

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);   
| improve this answer | |
  • This looks like a programming question and not a programming one! – Vérace Apr 19 '19 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.