PostgreSQL libpq "Integer out of range" error when sending integer as binary

Question

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?

Tometzky · Accepted Answer · 2009-08-20 07:55:56Z

3

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.

answered Aug 20, 2009 at 7:55

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Edward · Accepted Answer · 2009-08-19 21:00:34Z

1

Alright, it appears that it's an endian issue, which still doesn't quite explain it, since a little-endian (i.e. x86) 64 bit signed integer should fit in a big-endian 64 bit integer and vice versa, they'd just be corrupted. Swapping the endian on the integer yields the correct value, however. Swapping is done with the following function:

int64_t flip_endian( int64_t endi ) {
  char* bytearray;
  char swap;
  int64_t orig = endi;
  int i;

  bytearray = (char*)&orig;

  for( i = 0; i < sizeof( orig )/2; ++i ) {
    swap = bytearray[i];
    bytearray[i] = bytearray[ sizeof( orig ) - i - 1 ];
    bytearray[ sizeof( orig ) - i - 1 ] = swap;
  }

  return orig;

}

answered Aug 19, 2009 at 21:00

Edward

1,8261 gold badge16 silver badges33 bronze badges

Comments

Magnus Hagander · Accepted Answer · 2009-08-19 20:57:24Z

-1

I think it's getting passed over as 32-bit int, and then gets casted to 64-bit, because you're not telling libpq what the format is.

Try specifying an array for paramTypes, with the oid for int8 (which is 20) for the parameter.

answered Aug 19, 2009 at 20:57

Magnus Hagander

25.5k6 gold badges60 silver badges44 bronze badges

2 Comments

Edward Over a year ago

It's supposed to be able to determine that from the ::int8 cast. Also, it's definitely an endian issue, since flipping the endian not only gets rid of the error but actually results in correct insertions.

Magnus Hagander Over a year ago

Ah, I see. I should've thought of that :)

Collectives™ on Stack Overflow

PostgreSQL libpq "Integer out of range" error when sending integer as binary

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related