PostgreSQL result: numeric column type

[Sander van Schouwenburg (Sandworm)]

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'm not exactly sure what is the best way to handle this. One option would be to leave it a string, but this isn't very typesafe. Another option might be to be able to override the types somehow.

There already is a (ugly) way to do this, but I'd say this is a workaround:

$pq = $db->prepare('SELECT SUM(bigcol) AS sum FROM mytable');
$res = $pq->execute();
$res->data_type['sum'] = 'text'; // override float type
$row = $res->fetch();
$res->free();

At the moment I don't need the precision, so this is not a high priority for me. But still, this is a flaw.

[wouter bolsterlee (wbolster)]

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

[Sander van Schouwenburg (Sandworm) (sander-sinaasappel)]

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.

[wouter bolsterlee (wbolster)]

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.

[Sander van Schouwenburg (Sandworm) (sander-sinaasappel)]

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.

[wouter bolsterlee (wbolster)]

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

[wouter bolsterlee (wbolster)]

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

[Sander van Schouwenburg (Sandworm) (sander-sinaasappel)]

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.

[wouter bolsterlee (wbolster)]

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