提问人：Geert-Jan 提问时间：2/21/2023 更新时间：2/22/2023 访问量：242
如何使用 postgres 将 64 字符 / 256 位十六进制字符串转换为数字 （78,0）？
How to use postgres to cast a 64-char / 256-bit hex-string to a numeric(78,0)?
问：
尝试转换编码为 64 字符/256 位十六进制的 uint256 数字。 最终，结果应存储在一个数字 （78,0） 中，该数字足以对 uint256 进行编码。
对于较小的十六进制字符串，有非常直接的方法可以编码为 int4/int8，但这在这里不起作用。
因此，尝试将十六进制分成 4 个 64 位部分，这些部分都可以用 int8 表示。然后用一些位移将其粘合起来并完成。
不幸的是，没有意识到 int8 是签名的，所以这给了我错误的结果（下面的代码）。 我想我可以把六角形分成更小的部分，让它工作起来很麻烦，但现在我想知道一定有更好的方法。任何人？
-- 1. Split 256 bits into 4 seperate parts which are represented by int8's which can all hold 64 bits (4 * 64 = 256)
-- 2. cast int8 to numeric(78,0) which can hold a uint256, and some bitshifting (but not really since not supported by numeric) to get all parts
-- 3. add up the parts to arrive at the final number
--
-- WRONG RESULT: since int8 are signed.
select
val_a + val_b + val_c + val_d as value
from(
select
-- concat('x', substr(value, 0, 64))::bit(256) value_as_bits, -- all data as 256 bits
concat('x', substr(value, 0, 16))::bit(64)::int8::numeric(78,0) * 2^48::numeric(78,0) val_a,
concat('x', substr(value, 0 + 16, 16))::bit(64)::int8::numeric(78,0) * 2^32::numeric(78,0) val_b,
concat('x', substr(value, 0 + 32, 16))::bit(64)::int8::numeric(78,0) * 2^16::numeric(78,0) val_c,
concat('x', substr(value, 0 + 48, 16))::bit(64)::int8::numeric(78,0) val_d -- '-8257646570878140416' issue here since int8 is not unsigned
from (
select '000000000000000000000000000000000000000015b19218d66f231d61600000' as value -- hex representation of an unsigned 256 bit number
) as x
) as x;
;
答：
2赞
Lajos Arpad
2/21/2023
#1
介绍
你可以做类似的事情
('x'||lpad(the_hex_value,16,'0'))::bit(64)::bigint
以下各节将介绍甚至还不够的情况。bigint
示例架构
create table temp(
pk serial primary key,
ch char(1),
fk int,
ind int
);
insert into temp(ch, fk)
select unnest(
string_to_array(
regexp_replace('0123456789ABCD', E'(.)(?!$)', E'\\1 ', 'g'),
' '
)
), 1;
insert into temp(ch, fk)
select unnest(
string_to_array(
regexp_replace('123', E'(.)(?!$)', E'\\1 ', 'g'),
' '
)
), 2;
update temp tmp
set ind = (select count(*) from temp tmp2 where tmp2.fk = tmp.fk and tmp2.pk > tmp.pk);
解释：
- 我们创建一个名为
temppk是一个，稍后将用于确定每个数字的位置primary keych是表示十六进制数字的字符fk是一个外键，一个朝向原始表的单向顶点ind是一个最初未初始化的字段，它将变成倒排的数字索引，以便能够确定我们应该将 的数值乘以 16 的幂ch
- 我们用两行填充此表，在我们的示例中使用一些硬编码值来表示实际的十六进制数和外键
temp - 我们更新以计算和存储适当的值
tempind
查询
select fk, sum(digit)
from (
select fk, case
when ch = 'A' then 10
when ch = 'B' then 11
when ch = 'C' then 12
when ch = 'D' then 13
when ch = 'E' then 14
when ch = 'F' then 15
else ch::int
end
* power(16, RANK() OVER(ORDER BY ind) - 1) as digit
from temp) t
group by fk;
解释：
- 我们转换为一个数值
ch - 并将其乘以 16^（比倒置索引少 1）
- 我们分组依据，以确保我们得到每个值的单独值
fkfk - 最后，我们计算出每组的代表性数字值
sumfk
请注意，这里我们将这些值相加。如果您遇到任何问题，请随时将数字转换为文本并将它们与 .如果可能的话，您应该以更简单的方法为目标，并且只有在任何更简单的方法失败时才求助于这种方法。string_agg
评论
0赞
Geert-Jan
2/22/2023
好。永远不会想到这一点。这真的会比我正在做的快吗？
0赞
Lajos Arpad
2/22/2023
@Geert-Jan 可能不会，因为我的代码没有直接使用内置功能。我不会排除它，因此，如果性能是主要关注点，那么值得做一些基准测试来比较性能。这个答案的要点是你可以将你的六边形转换为一个，一般来说，如果可能的话，我会这样做。如果不可能，那么您将需要为自己实施一个解决方案，而我解释的方法就是这样一种后备方法。您是否尝试过（并且可能成功）使用转换？bigintbigint
0赞
Geert-Jan
2/22/2023
不幸的是，uint256（给定的）是 32 个字节，因此需要 4 个 bigint。否则，我同意会有更简单的方法。
1赞
Geert-Jan
2/22/2023
#2
回答我自己的问题：
-- Val is 64-char hex. Transform to Uint256 represented as a numeric(78,0)
CREATE OR REPLACE FUNCTION uint256_hash_to_numeric (val text) RETURNS numeric AS $$
select val_a + val_b + val_c + val_d + val_e
from(
select
concat('x00000000', substr(value, 1, 8))::bit(64)::int8::numeric(78,0) * 2^224::numeric(78,0) val_a,
concat('x00', substr(value, 9, 14))::bit(64)::int8::numeric(78,0) * 2^168::numeric(78,0) val_b,
concat('x00', substr(value, 23, 14))::bit(64)::int8::numeric(78,0) * 2^112::numeric(78,0) val_c,
concat('x00', substr(value, 37, 14))::bit(64)::int8::numeric(78,0) * 2^56::numeric(78,0) val_d,
concat('x00', substr(value, 51, 14))::bit(64)::int8::numeric(78,0) val_e
from (select val as value) as x
) as x
$$ LANGUAGE SQL;
我将对此进行性能测试，同时保持开放状态
评论