Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with data storage

Problem with data storage

From
"Francois Cote"
Date:
Hi,

I'm still doing test with PostgreSQL with C++ Builder using MicroOLAP DAC
and one big problem I have is with numerical data type.

Suppose the following table:
CREATE TABLE "TestNumbers"
(
   "TestInteger" integer,
   "TestReal" real,
   "TestMoney" money,
   "TestDouble" double precision,
   "TestNumeric" numeric(20,2)
);

Now, after adding a record with the value 22443018 for each fields I
finally have the following values:
TestInteger: 22443018
TestReal:    22443000
TestMoney:   {get a read error at 0x00000000, probably microOLAP DAC
related}
TestDouble:  {empty/no value displayed}
TestNumeric  {empty/no value displayed}

Using pgAdmin III, I see the same values.

Now Inserting data in the table using pgAdmin III:
INSERT INTO "TestNumbers"(
             "TestInteger", "TestReal", "TestMoney", "TestDouble",
"TestNumeric")
     VALUES ('22443018', '22443018', '22443018', '22443018', '22443018');

The results are in pgAdmin are:
TestInteger: 22443018
TestReal:    2.2443e+007
TestMoney:   -$20,506,654.96
TestDouble:  22443018
TestNumeric: 22443018.00

And looking at this record from C++ Builder, I have the following values:
TestInteger: 22443018
TestReal:    22443000
TestMoney:   -$20,506,654.96
TestDouble:  {empty/no value displayed}
TestNumeric: {empty/no value displayed}

I have made the same test with a simple Paradox table, and all values are
displayed correctly.

Does anybody knows why the rounding occurs, why the negative value and why
the blank value?


Thanks a lot
Francois

Re: Problem with data storage

From
"Francois Cote"
Date:
Hi,

One more thing about the money type.  At first, I thought that I had
overflowed the capacity. Sorry for my english here,  what I mean is, like
a signed Byte, the value range from 127 to -128, so what happens when you
put a value of 136?  That's right it become negative.  But, reading the
PostgreSQL documentation, the value of a Money type can range from
-92233720368547758.08 to +92233720368547758.07.  So my 22443018 is way in
the range to do that behavior, and since I tried adding value using also
an SQL query, I expected that the PostgreSQL engine could handle the value
correctly.

I already contacted MicroOLAP for my problems.  But for the money type,
since it doesn't work with an Insert query, I don't expect they can do
anything.

Thanks for any help you can provide.

Francois


On Fri, 04 Apr 2008 15:41:35 -0400, "Francois Cote" <francoisco@aei.ca>
wrote:

> Suppose the following table:
> CREATE TABLE "TestNumbers"
> (
>   "TestInteger" integer,
>   "TestReal" real,
>   "TestMoney" money,
>   "TestDouble" double precision,
>   "TestNumeric" numeric(20,2)
> );
>  Now, after adding a record with the value 22443018 for each fields I
> finally have the following values:
> TestInteger: 22443018
> TestReal:    22443000
> TestMoney:   {get a read error at 0x00000000, probably microOLAP DAC
> related}
> TestDouble:  {empty/no value displayed}
> TestNumeric  {empty/no value displayed}
>  Using pgAdmin III, I see the same values.
>  Now Inserting data in the table using pgAdmin III:
> INSERT INTO "TestNumbers"(
>             "TestInteger", "TestReal", "TestMoney", "TestDouble",
> "TestNumeric")
>     VALUES ('22443018', '22443018', '22443018', '22443018', '22443018');
>  The results are in pgAdmin are:
> TestInteger: 22443018
> TestReal:    2.2443e+007
> TestMoney:   -$20,506,654.96
> TestDouble:  22443018
> TestNumeric: 22443018.00
>  And looking at this record from C++ Builder, I have the following
> values:
> TestInteger: 22443018
> TestReal:    22443000
> TestMoney:   -$20,506,654.96
> TestDouble:  {empty/no value displayed}
> TestNumeric: {empty/no value displayed}
>  I have made the same test with a simple Paradox table, and all values
> are displayed correctly.
>  Does anybody knows why the rounding occurs, why the negative value and
> why the blank value?
>   Thanks a lot
> Francois
>

Re: Problem with data storage

From
"Mike Ellsworth"
Date:
On Tue, Apr 8, 2008 at 7:37 AM, Francois Cote <francoisco@aei.ca> wrote:
> Hi,
>
>  One more thing about the money type.  At first, I thought that I had
> overflowed the capacity. Sorry for my english here,  what I mean is, like a
> signed Byte, the value range from 127 to -128, so what happens when you put
> a value of 136?  That's right it become negative.  But, reading the
> PostgreSQL documentation, the value of a Money type can range from
> -92233720368547758.08 to +92233720368547758.07.  So my 22443018 is way in
> the range to do that behavior, and since I tried adding value using also an
> SQL query, I expected that the PostgreSQL engine could handle the value
> correctly.
>
>  I already contacted MicroOLAP for my problems.  But for the money type,
> since it doesn't work with an Insert query, I don't expect they can do
> anything.
>
>  Thanks for any help you can provide.

Francois,
You may want to search specifically within the hackers list as I
believe there have been 'challenges' for the money type for awhile.
I'm not saying there will be a resolution for your problem - but it
may give you more background on any known issues.