"what's the easiest way to represent a bytea as a single integer in postgresql?" Code Answer

another way is to extract the last 6 characters in hex representation, prepend an x and cast directly:

db=# select ('x' || right('x00000000000001'::bytea::text, 6))::bit(24)::int;
 int4
------
    1

.. which is a bit shorter than the get_byte() route, but is also an undocumented feature of postgresql. however, i quote tom lane here:

this is relying on some undocumented behavior of the bit-type input converter, but i see no reason to expect that would break. a possibly bigger issue is that it requires pg >= 8.3 since there wasn't a text to bit cast before that.

details in this related answer:

• convert hex in text representation to decimal number

this assumes that your setting of bytea_output is hex, which is the default since version 9.0. to be sure, you can test / set it for your session:

set bytea_output = 'hex';

more here:

• postgresql 9.x bytea representation in 'hex' or 'escape' for thumbnail images

performance

i ran a test (best of 10) on a table with 10k rows. get_byte() is actually a bit faster in postgres 9.1:

create temp table t (a bytea);
insert into t
select (12345670000000 + generate_series(1,10000))::text::bytea;

bit shifting is about as fast as multiplying / adding:

select 
 ('x' || right(a::text, 6))::bit(24)::int                           -- 34.9 ms
,(get_byte(a, 11) << 16) + (get_byte(a, 12) << 8) + get_byte(a, 13) -- 27.0 ms
,(get_byte(a, 11) << 16) | (get_byte(a, 12) << 8) | get_byte(a, 13) -- 27.1 ms
, get_byte(a, 11) * 65536 + get_byte(a, 12) * 256 + get_byte(a, 13) -- 27.1 ms
from t