What's the easiest way to represent a bytea as a single integer in PostgreSQL?

Question

I have a bytea column that contains 14 bytes of data. The last 3 bytes of the 14 contain the CRC code of the data. I would like to extract the CRC as a single integer to be stored in a new column.

How would I go about doing this?

To clarify, here's one way of doing it in Java:

int crc = ((rawData[len - 3] & 0xff) << 16 |
            (rawData[len - 2] & 0xff) << 8 |
            (rawData[len - 1] & 0xff)) & 0xffffff;

I'm hoping to find a solution without bit shifting, i.e. something like a method that accepts 4 bytes and converts them into an integer.

score 15 · Accepted Answer · edited May 23 '17 at 10:29

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

@CraigRinger: Sometimes the dark side of the force is just too tempting. :) — Erwin Brandstetter, Jun 20 '13 at 13:00
Would it not be more efficient to use bit shifting instead of multiplication? See my answer. — Zoltán, Jun 20 '13 at 14:30
Indeed. I also tested it now. I am accepting your answer because it provides a solution without byte-by-byte operations and shows its inefficiency. — Zoltán, Jun 20 '13 at 14:58
@Zoltán: For just three bytes, casting is a bit slower. For more bytes (up to 4 for `integer` or 8 for `bigint`, it starts winning since its performance is constant, while the performance of the three alts deteriorates. — Erwin Brandstetter, Jun 20 '13 at 15:01

Clodoaldo Neto · Answer 2 · 2013-06-20T18:46:14.153

5

select get_byte(b, 11) * 65536 + get_byte(b, 12) * 256 + get_byte(b, 13)
from (values ('12345678901234'::bytea)) s(b);
 ?column? 
----------
  3289908

edited Jun 20 '13 at 18:46

answered Jun 20 '13 at 12:11

Clodoaldo Neto

118,695
26
233
260

Your example bytea is only 13 bytes long. – Erwin Brandstetter Jun 20 '13 at 12:31
I was wondering why your calculation had different results. You have a typo: 2^16 = **65536**, not 65356. – Erwin Brandstetter Jun 20 '13 at 13:10

Zoltán · Answer 3 · 2016-07-21T08:33:12.890

4

Well if we're going to do byte-by-byte operations, then bit shifting is probably much more efficient than multiplication.

Based on Clodoaldo Neto's answer I would then say:

select (get_byte(arm_data, 11) << 16) |
       (get_byte(arm_data, 12) << 8) |
       (get_byte(arm_data, 13))
            from adsb_raw_message;

Does everyone agree?

edited Jul 21 '16 at 08:33

answered Jun 20 '13 at 14:29

Zoltán

21,321
14
93
134

1

Not efficient, but it is much more bit hacking which is in my opinion much more readable. – Diamondo25 Jul 03 '19 at 19:59

score 0 · Answer 4 · answered Jun 20 '13 at 09:18

0

If you want to store the CRC as a single integer in a separate column, I suggest converting it at insert- or update-time; then persist it together with the value for the bytea.

You can do this in your application/business layer or use an insert/update trigger to fill the CRC column.

answered Jun 20 '13 at 09:18

Beryllium

12,808
10
56
86

Yes, I am doing this in the business layer since the change has been implemented, however, the database already contains a valuable set of data so I was hoping to create a database migration script which would extract the CRC into the new column. – Zoltán Jun 20 '13 at 09:50

What's the easiest way to represent a bytea as a single integer in PostgreSQL?

4 Answers4

Performance

Linked