python - How can I cast an long NUMERIC integer into a bit string in PostgreSQL? -

python - How can I cast an long NUMERIC integer into a bit string in PostgreSQL? -

i'm trying calculate hamming distance pairs of long integers (20 digits each) in django app using pg_similarity extension postgres, , having hard time figuring out how this. django not seem have current bitstring field (which ideal, django_postgres seems defunct), trying cast integers bitstrings in sql query itself. current query:

sql = ''' select id, hamming( "hashstring"::bit(255), %s::bit(255) ) hamming_distance images having hamming_distance < %s order hamming_distance;'''

is throwing db error: cannot cast type numeric bit. doing wrong? else try?

per the manual, casting right approach if "long integer" "long integer" i.e. bigint / int8:

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

but (edit) you're asking how cast integer-only numeric bit. not simple, hold on.

you can't bitshift numeric either, can't bitshift 64-bit chunks, convert, , reassemble.

you'll have utilize partition , modulus instead.

given:

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

you can in 'bigint' chunks that, when multiplied max-long (9223372036854775807) times place value produce original value.

e.g. gets lowest 64-bits:

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

and gets chunks given value of 256 digits , exponents

with numval(v) (values ('1792913810350008736973055638379610855835'::numeric(40,0))) select exponent, floor(v / ('9223372036854775807'::numeric(256,0) ^ exponent) % '9223372036854775807'::numeric(40,0)) numval, generate_series(1,3) exponent;

you can reassemble original value:

with numval(v) ( values ('1792913810350008736973055638379610855835'::numeric(40,0)) ), chunks (exponent, chunk) ( select exponent, floor(v / ('9223372036854775807'::numeric(40,0) ^ exponent) % '9223372036854775807'::numeric(40,0))::bigint numval, generate_series(1,3) exponent ) select floor(sum(chunk::numeric(40,0) * ('9223372036854775807'::numeric(40,0) ^ exponent))) chunks;

so know it's decomposed correctly.

now we're working series of 64-bit integers, can convert each bitfield. because we're using signed integers, each has 63 important bits, so:

with numval(v) ( values ('1792913810350008736973055638379610855835'::numeric(40,0)) ), chunks (exponent, chunk) ( select exponent, floor(v / ('9223372036854775807'::numeric(40,0) ^ exponent) % '9223372036854775807'::numeric(40,0))::bigint numval, generate_series(1,3) exponent ) select exponent, chunk::bit(63) chunks;

gives bit values each 63-bit chunk. can reassemble them. there's no bitfield concatenation operator, can shift , bit_or, wrap sql function, producing monstrosity:

create or replace function numericint40_to_bit189(numeric(40,0)) returns bit(189) language sql $$ chunks (exponent, chunk) ( select exponent, floor($1 / ('9223372036854775807'::numeric(40,0) ^ exponent) % '9223372036854775807'::numeric(40,0))::bigint generate_series(1,3) exponent ) select bit_or(chunk::bit(189) << (63*(exponent-1))) chunks; $$;

which can seen in utilize here:

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

python sql django postgresql