PostgreSQL and numeric(19,4)

[MJFShark]

I'm having a problem and having a hard time debugging what is really going on.

The situation:
Pg table with a numeric(19,4) column.
Value in colum is inserted as 310000000 (310 million)
selecting this value gives 3100000000000 (4 digits added to the end that maybe should be decimals?)

Oddly enough: select 310000000::numeric(19,4) gives the correct result.

I think it has something to do with how the column type is interpreted as stCurrency vs stBigDecimal, but I'm having a hard time debugging this (the WITH statements are really hard to debug around!)

Can anyone confirm this issue? I'm still looking into it, but I'm not making much headway yet.

Here's a script for testing:

Code: Select all

create table pgnumtest(
  num numeric(19,4)
);
insert into pgnumtest values (310000000);
select num, 310000000::numeric(19,4) from pgnumtest;

The result from above for me shows:

Code: Select all

num           numeric   
------------- --------- 
3100000000000 310000000 

-Mark

[MJFShark]

I think I've got it.

In TZPostgreSQLResultSet.DefinePostgreSQLToSQLType there's a check to determine stCurrency vs stBigDecimal:

Code: Select all

    NUMERICOID: if TypeModifier <> -1 then begin
        ColumnInfo.Precision := (TypeModifier - VARHDRSZ) shr 16 and $FFFF;
        ColumnInfo.Scale     := (TypeModifier - VARHDRSZ)        and $FFFF;
        if (ColumnInfo.Scale <= 4) and (ColumnInfo.Precision <= sAlignCurrencyScale2Precision[ColumnInfo.Scale])
        then ColumnInfo.ColumnType := stCurrency
        else ColumnInfo.ColumnType := stBigDecimal;
        Exit;
      end;

In PostgreSQLToSQLType there's another check to determine stCurrency vs stBigDecimal:

Code: Select all

    NUMERICOID: begin
      Result := stBigDecimal;
      //see: https://www.postgresql.org/message-id/slrnd6hnhn.27a.andrew%2Bnonews%40trinity.supernews.net
      //macro:
      //numeric: this is ugly, the typmod is ((prec << 16) | scale) + VARHDRSZ,
      //i.e. numeric(10,2) is ((10 << 16) | 2) + 4
        if TypeModifier <> -1 then begin
          Scale := (TypeModifier - VARHDRSZ) and $FFFF;
          if (Scale <= 4) and ((TypeModifier - VARHDRSZ) shr 16 and $FFFF < sAlignCurrencyScale2Precision[Scale]) then
            Result := stCurrency
        end;
      end;

Note that the difference is that the second method uses just a less than for checking sAlignCurrencyScase2Precision while the first one uses less than or equal to.

Changing PostgreSQLToSQLType to use "<=" instead of "<" for the sAlignCurrencyScase2Precision seems to fix the issue but this should be tested in case a different fix (perhaps the first function should be using < instead of <=) is better.

Thanks as always
-Mark

[marsupilami]

Hello Mark,

your fix works like a charm. I included it in Zeos and tested it. There are no side effects.

Thank you for debugging and fixing this.

Best regards,

Jan