在PostgreSQL中将bytea转换为双精度 [英] Convert bytea to double precision in PostgreSQL

问题描述

我有一个数据库，其中的一个表存储从另一个系统收集的各种通用数据的blob（ bytea ）。 bytea 字段可以包含任何内容。为了知道如何解释数据，该表还具有一个格式字段。我编写了一个Java应用程序，以 byte [] 的形式从数据库中读取 bytea 字段，然后可以轻松地进行转换转换为 double [] 或 int [] 或使用 ByteBuffer表示的任何格式字段和各种视图（ DoubleBuffer ， IntBuffer 等）。

I have a database where one of the tables stores a blob (bytea) of all kinds of generic data collected from another system. The bytea field can have anything in it. In order to know how to interpret the data, the table also has a format field. I wrote a Java application to read the bytea field from the database as a byte[] and then I can easily convert it to double[] or int[] or whatever the format field says by using ByteBuffer and the various views (DoubleBuffer, IntBuffer, etc.).

现在，我需要在触发器函数中对数据库本身的数据进行一些操作，以保持与另一个表的完整性。我可以找到几乎可以想象到的任何数据类型的转换，但是找不到任何从 bytea （甚至是 bit ）到双精度并返回。可以将 bytea 分解，转换为位，然后转换为 int 或 bigint ，但不是双精度。例如， x'deadbeefdeadbeef':: bit（64）:: bigint 会毫无问题地转换为 -2401053088876216593 ，但 x'deadbeefdeadbeef':: bit（64）:: double precision 失败，并显示错误：无法将类型位转换为double precision，而不是给出IEEE 754的答案 -1.1885959257070704E148 。

Now I have the situation where I need to do some manipulation of the data on the database itself within a trigger function in order to maintain integrity with another table. I can find conversions for just about any data type imaginable, but I can't find anything for going from bytea (or even bit) to double precision and back. A bytea can be broken up, converted to bits, and then converted to an int or bigint, but not a double precision. For example, x'deadbeefdeadbeef'::bit(64)::bigint will convert to -2401053088876216593 with no problems, but x'deadbeefdeadbeef'::bit(64)::double precision fails with "ERROR: cannot cast type bit to double precision" instead of giving the IEEE 754 answer of -1.1885959257070704E148.

我找到了这个答案https://stackoverflow.com/a/11661849/5274457 ，它基本上实现了IEEE标准以将位转换为双精度，但是PostgreSQL中确实没有基本的转换功能来执行此操作？另外，在处理完数据并需要后，我还需要从 double precision 退回到 bytea

I found this answer https://stackoverflow.com/a/11661849/5274457, which basically implements the IEEE standard to convert bits to double, but is there really not a basic conversion function in PostgreSQL to do this? Plus, I need to go backwards as well from double precision to bytea when I'm done manipulating the data and need to update the tables, which this answer doesn't provide.

有什么想法吗？

推荐答案

好，我找到了答案。在PostgreSQL中，您可以使用Python编写函数。为了启用Python，您必须安装PostgreSQL安装所需的特定版本的Python，并将其在PATH环境变量中可用。通过查看安装说明，可以找到PostgreSQL安装所需的Python版本。我目前在Windows上使用PostgreSQL 9.6.5，它要求使用Python 3.3。我最初尝试使用最新的Python 3.6，但无法正常工作。我安装了适用于Windows的最新Python 3.3，即3.3.5。

Ok, I found an answer. In PostgreSQL, you can write functions using Python. In order to enable the use of Python, you have to install the specific version of Python needed by your installation of PostgreSQL and have it available in the PATH environment variable. You can find which version of Python your installation of PostgreSQL needs by looking at the installation notes. I'm currently using PostgreSQL 9.6.5 on Windows and it calls for Python 3.3. I initially tried the latest Python 3.6, but it wouldn't work. I settled with the latest Python 3.3 for Windows, which is 3.3.5.

安装Python后，您可以通过执行 CREATE EXTENSION在PostgreSQL中启用它plpython3u; 在您的数据库中，如此处 https：/ /www.postgresql.org/docs/current/static/plpython.html 。从那里，您可以使用Python主体编写任何函数。

After installing Python, you enable it in PostgreSQL by executing CREATE EXTENSION plpython3u; on your database as documented here https://www.postgresql.org/docs/current/static/plpython.html. From there, you can write any function with Python bodies.

对于我的具体情况，是将 bytea 转换为 double precision [] 然后，我编写了以下函数：

For my specific case to convert from bytea to double precision[] and back, I wrote the following functions:

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

在我的情况下，所有双打都存储在little endian中，所以我使用< 。我还将 struct 模块的导入缓存在全局字典中，如 https中所述：//stackoverflow.com/a/15025425/5274457 。我使用GD而不是SD，因为我希望导入可以在我可能编写的其他函数中使用。有关GD和SD的信息，请参见 https：//www.postgresql。 org / docs / current / static / plpython-sharing.html 。

In my case, all the doubles are stored in little endian, so I use <. I also cache the import of the struct module in the global dictionary as described in https://stackoverflow.com/a/15025425/5274457. I used GD instead of SD because I want the import available in other functions I may write. For information about GD and SD, see https://www.postgresql.org/docs/current/static/plpython-sharing.html.

要了解它的作用，知道我数据库中的blob以小端序存储，

To see it in action knowing the blobs in my database are stored as little 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'用小尾数法表示。

where 'efbeaddeefbeadde' is 'deadbeefdeadbeef' in little endian.

这篇关于在PostgreSQL中将bytea转换为双精度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！