Geek Answers Handbook

Convert bytea to double precision in PostgreSQL

I have a database in which one of the tables stores blob ( bytea) of all kinds of common data collected from another system. A field byteamay have something in it. To know how to interpret the data, the table also has a format field. I wrote a Java application to read a field byteafrom a database in a view byte[], and then I can easily convert it to double[]or int[]or something into a format field using ByteBuffervarious views ( DoubleBuffer, IntBufferetc.).

Now I have a situation where I need to do some manipulations with the data in the database itself in the trigger function in order to maintain integrity with another table. I can find conversions for almost any type of data that I can imagine, but I can not find anything to go from bytea(or even bit) to double precisionand back. A byteacan be broken, converted to bits, and then converted to intor bigint, but not to double precision. For example, it x'deadbeefdeadbeef'::bit(64)::bigintwill convert to without problems -2401053088876216593, but x'deadbeefdeadbeef'::bit(64)::double precisionfails with "ERROR: cannot use the type bit for double precision" instead of providing an IEEE 754 response -1.1885959257070704E148.

I found this answer https://stackoverflow.com/a/166268/ which is basically implementing the IEEE standard for converting bits to double, but is there really no conversion function in PostgreSQL to do this? In addition, I need to go back from double precisionto byteawhen I finished the data manipulation and you need to update the tables that this answer does not provide.

Any ideas?

+6
source share
1 answer

, . PostgreSQL Python. Python, Python, PostgreSQL, PATH. , Python PostgreSQL, . PostgreSQL 9.6.5 Windows, Python 3.3. Python 3.6, . Python 3.3 Windows, 3.3.5.

Python PostgreSQL, CREATE EXTENSION plpython3u; , https://www.postgresql.org/docs/current/static/plpython.html. Python.

bytea double precision[] :

CREATE FUNCTION bytea_to_double_array(b bytea)
    RETURNS double precision[]
    LANGUAGE 'plpython3u'
AS $BODY$
  if 'struct' in GD:
    struct = GD['struct']
  else:
    import struct
    GD['struct'] = struct

  return struct.unpack('<' + str(int(len(b) / 8)) + 'd', b)
$BODY$;

CREATE FUNCTION double_array_to_bytea(dblarray double precision[])
    RETURNS bytea
    LANGUAGE 'plpython3u'
AS $BODY$
  if 'struct' in GD:
    struct = GD['struct']
  else:
    import struct
    GD['struct'] = struct

  # dblarray here is really a list.
  # PostgreSQL passes SQL arrays as Python lists
  return struct.pack('<' + str(int(len(dblarray))) + 'd', *dblarray)
$BODY$;

, <. struct , fooobar.com/questions/376634/.... GD SD, , , . GD SD . https://www.postgresql.org/docs/current/static/plpython-sharing.html.

, , endian,

SELECT bytea_to_double_array(decode('efbeaddeefbeadde', 'hex')), encode(double_array_to_bytea(array[-1.1885959257070704E148]), 'hex');

, ,

bytea_to_double_array    | encode
double precision[]       | text
-------------------------+------------------
{-1.18859592570707e+148} | efbeaddeefbeadde

'efbeaddeefbeadde' 'deadbeefdeadbeef'.

+1

More articles:


All Articles