Convert bigint to bytea, but swap the byte order

Question

I have a PostgreSQL table that I want to alter a column from bigint to bytea byte to hold more data. I am thinking using the following sequence:

1. alter table mytable add new_column
2. update mytable set new_column = int8send(old_column)
3. alter table drop old_column
4. alter table rename new_column to old_column

The above sequence works, the only problem is that I want the byte sequence in the bytea to be reversed. For example, if a value in old_column is 0x1234567890abcdef, the above sequence would generate \0224Vx\220\253\315\357, but I want it to be \357\315\253\220xV4\022. Seems like the resulting bytea uses the big-endian order from originating bigint.

Is there an easy way to do that without writing a program? I was looking for a swap64() sort of function in PostgreSQL but failed to find one.

Answer 1

It is possible to byte-swap without plpgsql code using regexp extractions on the hexadecimal representation. Here's an example to swap a bigint constant, assuming SET standard_conforming_strings to ON (the default with PG 9.1)

select regexp_replace( lpad(to_hex(x'123456789abcd'::bigint),16,'0'),
 '(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)',
 '\8\7\6\5\4\3\2\1');

It returns cdab896745230100. Then apply decode(value, 'hex') to convert that to a bytea.

The whole type conversion could actually be done in a single SQL statement:

ALTER TABLE mytable ALTER COLUMN old_column TYPE bytea
  USING decode(
    regexp_replace( lpad(to_hex(old_column), 16,'0'),
 '(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)(\w\w)',
 '\8\7\6\5\4\3\2\1')
  , 'hex');