how to convert numeric to byte as similer to java in postgres

Question

I am trying to replicate some java code in Postgres.

The operation, I need to perform is to convert Java BigInteger to byte array

Java Code:

public class Main {
    public static void main(String[] args) {
         BigInteger n = new BigInteger("1677259342285725925376");
         System.out.println(Arrays.toString(n.toByteArray()));
    }

}

Output: [90, -20, -90, 53, 78, -38, 2, -128, 0]

Postgres Code:

select  numeric_send(1677259342285725925376);

Output: "\000\006\000\005\000\000\000\000\000\020\036-$~\013)\012 \025\000"

Used numeric_send to convert numeric to bytea http://www.leadum.com/downloads/dbscribe/samples/postgresql/web_modern/function/main/643567399.html

To check output as in Java Console, I wrote below anonymous block

do 
$$
declare
bytes bytea;
begin
bytes := numeric_send(1677259342285725925376);
for i in 0..length(bytes)-1 loop
 raise notice '%', get_byte(bytes,i);
end loop;
end; $$

Now output printed as:

NOTICE:  0
NOTICE:  6
NOTICE:  0
NOTICE:  5
NOTICE:  0
NOTICE:  0
NOTICE:  0
NOTICE:  0
NOTICE:  0
NOTICE:  16
NOTICE:  30
NOTICE:  45
NOTICE:  36
NOTICE:  126
NOTICE:  11
NOTICE:  41
NOTICE:  10
NOTICE:  32
NOTICE:  21
NOTICE:  0

According to me output should be same as I am converting along to byte array in both. Please help how can I achieve the same.

Thanks

Why would you need that inside Postgres? What is the underlying problem you are trying to solve with that? — , Aug 23 '19 at 08:26
@a_horse_with_no_name We were using `plpythonu` extension for such operations in postgres, As `plpythonu` is not supported on `AWS RDS`. So, we need to create these functions in postgres. — Tajinder, Aug 23 '19 at 08:31

score 4 · Answer 1 · answered Aug 23 '19 at 08:24

PostgreSQL's numeric and Java's java.math.BigInteger have different binary representation.

numeric is a binary coded decimal, and you have the varlena header in there as well.

Avoid using the internal binary representation of data types wherever you can — it may depend on things like the endianness of the machine's architecture.

score 0 · Accepted Answer · answered Aug 28 '19 at 05:38

I have performed the following steps to convert numeric to a byte array.

-- Convert numeric to bit
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 IMMUTABLE;

-- conversion for bit(8) to integer with sign
CREATE OR REPLACE FUNCTION bit_to_integer(b BIT(8))
  RETURNS INTEGER  AS $$
DECLARE
  result int;
BEGIN
  result := b::integer;
  result := case when result > 127 then result-256 else result end;
  RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- removce padding from bit varying
CREATE OR REPLACE FUNCTION ltrim_bitvar(bv BIT VARYING)
  RETURNS text  AS $$
DECLARE
  result text;
  fill int;
BEGIN
  result := ltrim(bv::text,'0');
  fill := 8-length(result)%8;
  result := lpad(result,length(result)+fill,'0') ;
  return result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--converting bit var text to byte array
CREATE OR REPLACE FUNCTION btext_to_bytea(b text)
  RETURNS bytea AS
$BODY$
DECLARE
  bytes bytea;
  temp integer;
  b_len integer;
BEGIN
  b_len := length(b)/8;
  bytes = lpad('',b_len,'0')::bytea;
  for i in 1..b_len loop
   temp = substring(b,(i*8)-8 + 1,8)::bit(8)::integer;
   --raise notice '%', temp;
   bytes := set_byte(bytes, i-1, temp);
  end loop;
  return bytes;

END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE;

-- converting numeric to bytea
CREATE OR REPLACE FUNCTION num_to_bytea(n numeric)
  RETURNS bytea AS
$BODY$

BEGIN
 return btext_to_bytea(ltrim_bitvar(numeric_to_bit(n)));
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE;

Creat above pre requisite functions.

do 
$$
declare
bytes bytea;
begin
bytes := num_to_bytea(1677259342285725925376::numeric);
for i in 0..length(bytes)-1 loop
 raise notice '%',  case when get_byte(bytes,i) > 127 then get_byte(bytes,i)-256 else get_byte(bytes,i) end ;
end loop;
end; $$

Output:

NOTICE:  90
NOTICE:  -20
NOTICE:  -90
NOTICE:  53
NOTICE:  78
NOTICE:  -38
NOTICE:  2
NOTICE:  -128
NOTICE:  0

how to convert numeric to byte as similer to java in postgres

2 Answers2