在PostgreSQL中将bytea表示为单个整数的最简单的方法是什么? [英] What's the easiest way to represent a bytea as a single integer in PostgreSQL?
问题描述
我有一个 bytea
列,其中包含14个字节的数据。 14的最后3个字节包含数据的CRC码。
我将如何去做这个?
要澄清一下,这里有一种在Java中做的方法:
int crc =((rawData [ len-3]& 0xff)<16 |
(rawData [len-2]& 0xff)<8 |
(rawData [len_1]& 0xff) )& 0xffffff;
我希望找到一个没有位移位的解决方案,
另一种方法是提取 hex
x
并直接投射: db =#SELECT('x'|| right('\x00000000000001':: bytea :: text,6)):: bit(24)
int4
------
1
。 。这比 get_byte()
路径稍短,但也是PostgreSQL的未记录的特性。不过,我引用 Tom Lane这里:
这取决于位类型输入
转换器的一些未记录的行为,但是我没有理由期望这会破坏。一个可能的
更大的问题是,它需要PG> = 8.3,因为之前没有一个文本
比特转换。
相关答案中的详细信息:
这假设你的 bytea_output
的设置是 hex
,这是从版本9.0开始的默认值。当然,你可以测试/设置你的会话:
SET bytea_output ='hex';
更多此处:
性能
我在一个10k行的表上运行一个测试(最好是10)。 get_byte()
实际上在Postgres 9.1中有点快:
CREATE TEMP TABLE t(a bytea);
INSERT INTO t
SELECT(12345670000000 + generate_series(1,10000)):: text :: bytea;
位移大约与乘/ >
SELECT
('x'|| right(a :: text,6)):: bit int -34.9ms
,(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
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.
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:
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:
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
这篇关于在PostgreSQL中将bytea表示为单个整数的最简单的方法是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!