python - 如何在 PostgreSQL 中将长整数 NUMERIC 转换为位字符串？

Question

我正在尝试使用 Postgres 的 pg_similarity 扩展在 Django 应用程序中计算长整数对(每个 20 位数字)的汉明距离，但我很难弄清楚如何执行此操作。 Django 似乎没有当前的 BitString 字段(这很理想，但 django_postgres 似乎已经失效)，所以我试图在 SQL 查询本身中将整数转换为位串。我当前的查询:

    sql = ''' SELECT id, hamming(
        "HashString"::BIT(255),
        %s::BIT(255)
    ) as hamming_distance
    FROM images
    HAVING hamming_distance < %s
    ORDER BY hamming_distance;'''

正在引发数据库错误:无法将类型数字转换为位。我究竟做错了什么？我还能尝试什么？

Answer 1

根据 the manual ，如果您的“长整数”实际上是一个“长整数”，即 bigint/int8，则转换是正确的方法:

regress=> SELECT ('1324'::bigint)::bit(64);
                               bit                                
------------------------------------------------------------------
 0000000000000000000000000000000000000000000000000000010100101100
(1 row)

但是(编辑)您实际上是在询问如何将仅限整数的 numeric 转换为 bit。没那么简单，等等。

您也无法对数字进行位移，因此无法轻松地将其位移为 64 位 block 、转换和重新组合。

您必须改用除法和模数。

给定:

SELECT '1792913810350008736973055638379610855835'::numeric(40,0);

您可以在“bigint” block 中获取它，当乘以 max-long (9223372036854775807) 乘以它们的位值时会产生原始值。

例如这得到最低的 64 位:

SELECT ('1792913810350008736973055638379610855835'::numeric(40,0) / '9223372036854775807'::numeric(256,0)) % '9223372036854775807'::numeric(40,0);

这会获取给定值(最多 256 位)的所有 block 及其指数

WITH numval(v) AS (VALUES ('1792913810350008736973055638379610855835'::numeric(40,0)))
SELECT exponent, floor(v / ('9223372036854775807'::numeric(256,0) ^ exponent) % '9223372036854775807'::numeric(40,0)) from numval, generate_series(1,3) exponent;

您可以将其重新组装成原始值:

WITH
  numval(v) AS (
    VALUES ('1792913810350008736973055638379610855835'::numeric(40,0))
  ),
  chunks (exponent, chunk) AS (
     SELECT exponent, floor(v / ('9223372036854775807'::numeric(40,0) ^ exponent) % '9223372036854775807'::numeric(40,0))::bigint from numval, generate_series(1,3) exponent
  )
SELECT floor(sum(chunk::numeric(40,0) * ('9223372036854775807'::numeric(40,0) ^ exponent))) FROM chunks;

所以我们知道它被正确分解了。

现在我们正在处理一系列 64 位整数，我们可以将每个整数转换为一个位域。因为我们使用的是有符号整数，每个整数只有 63 位有效位，所以:

WITH
  numval(v) AS (
    VALUES ('1792913810350008736973055638379610855835'::numeric(40,0))
  ),
  chunks (exponent, chunk) AS (
     SELECT exponent, floor(v / ('9223372036854775807'::numeric(40,0) ^ exponent) % '9223372036854775807'::numeric(40,0))::bigint from numval, generate_series(1,3) exponent
  )
SELECT
  exponent,
  chunk::bit(63)
FROM chunks;

为我们提供每个 63 位 block 的位值。然后我们可以重新组装它们。没有位域连接运算符，但我们可以移位和 bit_or，然后将其包装到一个 SQL 函数中，产生怪异的结果:

CREATE OR REPLACE FUNCTION numericint40_to_bit189(numeric(40,0)) RETURNS bit(189)
LANGUAGE sql
AS
$$
    WITH
      chunks (exponent, chunk) AS (
         SELECT exponent, floor($1 / ('9223372036854775807'::numeric(40,0) ^ exponent) % '9223372036854775807'::numeric(40,0))::bigint 
         FROM generate_series(1,3) exponent
      )
    SELECT
      bit_or(chunk::bit(189) << (63*(exponent-1)))
    FROM chunks;
$$;

可以在这里看到它的使用:

regress=> SELECT numericint40_to_bit189('1792913810350008736973055638379610855835');
                                                                                    numericint40_to_bit189                                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010101000100110101101010001110110110101001111100011100011110000010110
(1 row)