Sql – PostgreSQL libpq “Integer out of range” error when sending integer as binary

clibpqpostgresql

I'm attempting to insert some integers into a Postgres table with the following somewhat simple code.

#include <libpq-fe.h>
#include <stdio.h>
#include <stdint.h>

int main() {
  int64_t i = 0;
  PGconn * connection = PQconnectdb( "dbname='babyfood'" );
  if( !connection || PQstatus( connection ) != CONNECTION_OK )
    return 1;
  printf( "Number: " );
  scanf( "%d", &i );

  char * params[1];
  int param_lengths[1];
  int param_formats[1];
  param_lengths[0] = sizeof( i );
  param_formats[0] = 1;
  params[0] = (char*)&i;
  PGresult * res =  PQexecParams( connection, 
                                  "INSERT INTO intlist VALUES ( $1::int8 )",
                                  1,
                                  NULL,
                                  params,
                                  param_lengths,
                                  param_formats,
                                  0 );
  printf( "%s\n", PQresultErrorMessage( res ) );
  PQclear( res );
  PQfinish( connection );
  return 0;
}

I get the following results:

Number: 55
ERROR:  integer out of range

Number: 1
ERROR:  integer out of range

I'm pretty sure that an int64_t will always fit in an 8 byte integer on any sane platform. What am I doing wrong?

Best Answer

Instead of:

params[0] = (char*)&i;

you should use:

#include <endian.h>
/* ... */
int64_t const i_big_endian = htobe64(i);
params[0] = (char*)&i_big_endian;

A htobe64 function will switch endianness on little-endian, and do nothing on big-endian.

Ditch your flip_endian function, as it would make your program incompatible with big-endian/bi-endian computers, like PowerPC, Alpha, Motorola, SPARC, IA64 etc. Even if your program does not expect to be run on them it is a bad style, slow and error prone.

Related Topic