使用 libpq 在表中插入浮点数 [英] Inserting a floating point number in a table using libpq

问题描述

我正在使用 libpq 在表中插入一个浮点数.我收到这个错误 INSERT failed: ERROR: In enough data left in message.

I am inserting a floating point number in a table using libpq. I am getting this error INSERT failed: ERROR: insufficient data left in message.

这是我的代码库中的相应片段:

Here is the corresponding snippet from my code base:

printf ("Enter write parameter_value");
scanf("%f", &parameter_value);

char *stm = "INSERT INTO write_reg_set (parameter_value) VALUES ($1::double precision)";

int nparam = 1;

//set the values to use
const char *values[1] = {(char *)&parameter_value};

//calculate the lengths of each of the values
int lengths[1] = {sizeof(parameter_value)};

//state which parameters are binary
int binary[1] = {1};

PGresult *res = PQexecParams(conn,
                     stm,
                     nparam,  //number of parameters
                     NULL,    //ignore the Oid field
                     values,  //values to substitute $1 and $2 and so on
                     lengths, //the lengths, in bytes, of each of the parameter values
                     binary,  //whether the values are binary or not
                     0);      //we want the result in text format

if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));
    exit_nicely(conn,res);
}
PQclear(res);

推荐答案

您的代码中有两个错误:

There are two errors in your code:

• 您正在尝试发送二进制数据，但您没有告诉 PQexecParams 它是哪种类型.

那行不通.缺少类型信息，PostgreSQL 将使用类型 unknown 并将其视为字符串.这意味着您的二进制表示将被提供给 float8in 函数，该函数将字符串转换为双精度值，这将非常失败.这可能就是您所观察到的.

That cannot work. Lacking type information, PostgreSQL will use the type unknown and treat it as string. That means that your binary representation will be fed to the float8in function that converts strings to double precision values, which will fail horribly. This is probably what you are observing.

您必须使用包含 701 的 Oid[] 的第四个参数(或者 FLOAT8OID 如果您更愿意使用 PostgreSQL 的 #definecode>，但您必须#include 和 .

You will have to use a fourth parameter with an Oid[] that contains 701 (or FLOAT8OID if you'd rather use PostgreSQL's #define, but you'd have to #include <postgres.h> and <catalog/pg_type.h> for that).

您错误地认为 double precision 类型的 PostgreSQL 二进制表示是您的客户端机器上使用的 double 的二进制格式.

You mistakenly assume that PostgreSQL's binary representation of the double precision type is the binary format for double in use on your client machine.

如果您的程序在 big-endian 机器上运行，这可能会意外地工作，因为实际上现在每个架构都使用 IEEE 浮点数.

This might accidentally work if your program is running on a big-endian machine, since virtually every architecture these days uses IEEE floating point numbers.

如果你阅读源代码，你会发现在src/backend/libpq/pqformat.c中的pq_sendfloat8中定义了PostgreSQL的在线二进制格式>，它调用 pq_sendint64，将 8 字节值转换为网络字节顺序(与 big-endian 表示相同).

If you read the source code, you'll find that PostgreSQL's over-the-wire binary format ist defined in pq_sendfloat8 in src/backend/libpq/pqformat.c, which calls pq_sendint64, which converts the 8-byte value to network byte order (which is the same as big-endian representation).

所以你必须定义一个类似于这样的转换函数:

So you'd have to define a conversion function similar to this:

static void to_nbo(double in, double *out) {
    uint64_t *i = (uint64_t *)∈
    uint32_t *r = (uint32_t *)out;

    /* convert input to network byte order */
    r[0] = htonl((uint32_t)((*i) >> 32));
    r[1] = htonl((uint32_t)*i);
}

那么您的代码可能如下所示:

Then your code could look like this:

Oid types[1];
double converted;

...

types[0] = FLOAT8OID;
to_nbo(value, &converted);
values[0] = (char *)&converted;

但坦率地说，使用文本表示会容易得多.这将使您的代码独立于 PostgreSQL 内部结构，并且可能不会慢很多.

But frankly, it would be much easier to use the text representation. That will make your code independent of PostgreSQL internals and probably is not so much slower.

它看起来不像，但是如果 double precision 值是从其他地方的 PostgreSQL 表中提取的，您可以设置 extra_float_digits= 3这样您就可以保证在将值转换为其字符串表示时不会丢失任何精度..

It doesn't look like it, but if the double precision values are pulled from a PostgreSQL table somewhere else, you could set extra_float_digits= 3 so that you are guaranteed not to lose any precision when the values are converted to their string representation..

这篇关于使用 libpq 在表中插入浮点数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！