PostgreSQL result: numeric column type

> The 'numeric' column type in a PostgreSQL result is now converted to
> float. However, 'numeric' doesn't necessarily mean it is a floating
> point value. Doing a SUM() over some BIGINT column also reports a
> 'numeric' type, and by converting it to a float I've lost precision
> information.

I don't see how one could possibly lose precision in this case. A mathemathical property of integer numbers is that addition, subtraction and multiplication (but not division!) of two integers will always result in an integer. I suspect PostgreSQL somehow detects/assumes that summing over BIGINT may cause numerical overflow, and converts the numbers to floats (which can keep higher numbers).

It could well be that I'm overlooking something here, though. If so, please tell me how (and why) you lost numerical precision in the case you described.

My point is: PostgreSQL does NOT convert the value to float. It is just a bigint, only the result from pg_field_type() will return 'numeric'. The string is just a bigint. But since anewt sees 'numeric' and thinks thats a float, it will convert it to a float with less precision.

The following code:

$rs = pg_query('SELECT SUM(large_value*9999999) FROM sometable');
$rs = pg_query($sql);
echo pg_field_type($rs, 0);
$value = pg_fetch_result($rs, 0);
echo $value;
echo (float)$value; // <-- this is what anewt does

Will return the following output (adjusted for whitespace):

numeric
107629173119941611714
1.0762917311994E+20

Which proves that some precision is lost.

Only when I made the input one factor larger did postgres complain about an overflow.

This snippet of PHP code:

  $x = 107629173119941611714;
  var_dump($x);
  var_dump((int) $x);

...produces this output:

  float(1.0762917312E+20)
  int(0)

So this has nothing to do with PostgreSQL nor how Anewt handles database result sets. The numbers you're using are just too big to fit in a PHP integer value.

That might be right, but that does not mean information should be lost. You might for example just output this value, or, in my case, further handle the value using GMP.

...in which case you probably want to cast to a string explicitly (not sure how to do this with PostgreSQL) instead of treating the value as a numeric value.

Sander, see bug #361805. Would something like that also be possible with PostgreSQL? (With MySQLi it can be fixed, with the old MySQL module it cannot.)

I think you mean bug #503538 ? Because I don't see how bug #361805 is relevant.

Still, I'm not really sure what you mean, but postgresql has some functions like pg_field_ptrlen() which return the printed length of the field. Maybe that's useful. But then again, you could probably use strlen() for that.

Sander, is there any chance of you providing a patch for this issue?