Inserting a floating point number in a table using libpq

Question

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

Answer 1

There are two errors in your code:

• You are trying to send binary data, but you don't tell PQexecParams which type it is.

  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.

  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).

• 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.

  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.

  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;

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.

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..