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

Per the manual, casting is the correct approach if your "long integer" is actually a "long integer" i.e. bigint / int8:

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

but (edit) you're actually asking how to cast an integer-only numeric to bit. Not so simple, hold on.

You can't bitshift numeric either, so you can't easily bitshift it into 64-bit chunks, convert, and reassemble.

You'll have to use division and modulus instead.

Given:

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

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

e.g. this gets the lowest 64-bits:

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

and this gets all the chunks for a given value of up to 256 digits and their exponents

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;

You can reassemble this into the original value:

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;

so we know it's decomposed correctly.

Now we're working with a series of 64-bit integers, we can convert each into a bitfield. Because we're using signed integers, each only has 63 significant bits, so:

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;

gives us the bit values for each 63-bit chunk. We can then reassemble them. There's no bitfield concatenation operator, but we can shift and bit_or, then wrap it into an SQL function, producing the monstrosity:

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;
$$;

which can be seen in use here:

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

Thanks for the initial answer Craig Ringer! Here is a correct version of the function. It supports up to 300 bits and can be expanded if needed.

CREATE OR REPLACE FUNCTION numeric_to_bit(NUMERIC)
  RETURNS BIT VARYING AS $$
DECLARE
  num ALIAS FOR $1;
  -- 1 + largest positive BIGINT --
  max_bigint NUMERIC := '9223372036854775808' :: NUMERIC(19, 0);
  result BIT VARYING;
BEGIN
  WITH
      chunks (exponent, chunk) AS (
        SELECT
          exponent,
          floor((num / (max_bigint ^ exponent) :: NUMERIC(300, 20)) % max_bigint) :: BIGINT
        FROM generate_series(0, 5) exponent
    )
  SELECT bit_or(chunk :: BIT(300) :: BIT VARYING << (63 * (exponent))) :: BIT VARYING
  FROM chunks INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

Here's something that will help convert arbitrary length numerics to varbits in PostgreSQL.

-- function to convert numerics to bit(n)
create or replace function numeric2bit(n numeric, typemod integer) returns varbit as
$$
declare
    r varbit := b'';
    width int := 8;
    modulo numeric := 2 ^ width;
    remainder bigint := 0;
begin
    while typemod > 8 loop
        remainder := n % modulo;
        -- make sure to adjust the size of the bit(n) type to match the width var
        r := remainder::bit(8) || r;
        n := (n - remainder) / modulo;
        typemod := typemod - 8;
    end loop;

    if typemod > 0 then
        while typemod != 0 loop
            remainder := n % 2;
            -- make sure to adjust the size of the bit(n) type to match the width var
            r := remainder::bit || r;
            n := (n - remainder) / 2;
            typemod := typemod - 1;
        end loop;
    end if;

    return r;
end
$$ language plpgsql;

-- function to convert numerics to varbit
create or replace function numeric2varbit(n numeric) returns varbit as
$$
begin
    return numeric2bit(n, ceil(log(2, n))::integer);
end
$$ language plpgsql;

-- casts for numeric::bit(n)
create cast (numeric as bit) with function numeric2bit(numeric, integer);

-- casts for numeric::varbit
create cast (numeric as varbit) with function numeric2varbit;

do
$$
begin
    assert 2482139123891291248192194912049102::numeric::varbit =
           b'111101001100001000000000001000011110001001101100011101100001101111111111100000001101110010011011101011111001110';
    assert 255::numeric::bit(16) = b'0000000011111111';
end
$$;

Try it with python:

sql = sorted(([hamming("HashString", %s,image.id) for image in Image.objects.all() if hamming("HashString",%s) < %s])