pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRE: [ADMIN] float4
5+ messages / 4 participants
[nested] [flat]
* RE: [ADMIN] float4
@ 1999-06-28 20:53 JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
0 siblings, 2 replies; 5+ messages in thread
From: JT Kirkpatrick @ 1999-06-28 20:53 UTC (permalink / raw)
To: 'Don Garrett' <garrett@bgb-consulting.com>; +Cc: 'pgsql-interfaces@hub.org' <pgsql-interfaces@hub.org>
ouch. hard to think of dollars only when you are paying vendors,
technicians, billing customers, etc. surely i won't have to make an int
field for the dollars and an int field for the cents!!! the money field
would work nicely if the odbc driver would handle it correctly!
i forwarded this to the odbc list -- any idea why a money field can't be
used from msaccess97 through the odbc??
thanks for your help!
jt
-----Original Message-----
From: Don Garrett [SMTP:garrett@bgb-consulting.com]
Sent: Monday, June 28, 1999 4:25 PM
To: JT Kirkpatrick
Subject: Re: [ADMIN] float4
You shouldn't use a float at all for exact amounts. Floats handle
arbitrary precision, but do it rounding off values somewhat. The way the
round off works is based on the (base 2) internal representation and so
it never quite seems to make sense in decimal. It's possible on some
machines/float formats to have "if (1.0 + 1.0 = 2.0)" evaluate to false.
When I first started programming, I did a very simple custom
accounting package using floats.... and had to go back and rewrite all
of it.
You will do better to use integers (no rounding at all) and consider
the contents to be the number of pennies involved, not dollars.
JT Kirkpatrick wrote:
>
> we have a database (linux, 6.4.2, new odbc, connections from msaccess /
> win98) -- access doesn't fully appreciate the monetary type in postgres,
so
> we made all monetary fields FLOAT4's. weird stuff -- or maybe not to you
> -- we insert 23516.69 into a float4 field directly in psql, and then
select
> it, and it shows 23516.7. this is not good for a field used for MONEY!!!
> what are the limitations of float4? should i have used float8?
>
> thanks for any help you can offer. . .
>
> jt
--
Don Garrett dgarrett@acm.org
BGB Consulting http://www.bgb-consulting.com/garrett
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [ADMIN] float4
@ 1999-06-28 21:06 Don Garrett <garrett@bgb-consulting.com>
parent: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
1 sibling, 0 replies; 5+ messages in thread
From: Don Garrett @ 1999-06-28 21:06 UTC (permalink / raw)
To: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>; +Cc: 'pgsql-interfaces@hub.org' <pgsql-interfaces@hub.org>
No, use one field for both. But use the UNIT pennies. To get the
number of dollars, divide by 100, to get the number of pennies (without
dollars) do (value % 100).
You can use the single value for additions, subtractions, totals and
everything else, just as if it were type money. In fact, this is
probably how type money stores things internally.
And no, I don't know anything about ODBC, so I have no idea why you
are having trouble with the money type.
JT Kirkpatrick wrote:
>
> ouch. hard to think of dollars only when you are paying vendors,
> technicians, billing customers, etc. surely i won't have to make an int
> field for the dollars and an int field for the cents!!! the money field
> would work nicely if the odbc driver would handle it correctly!
>
> i forwarded this to the odbc list -- any idea why a money field can't be
> used from msaccess97 through the odbc??
>
> thanks for your help!
>
> jt
>
> -----Original Message-----
> From: Don Garrett [SMTP:garrett@bgb-consulting.com]
> Sent: Monday, June 28, 1999 4:25 PM
> To: JT Kirkpatrick
> Subject: Re: [ADMIN] float4
>
> You shouldn't use a float at all for exact amounts. Floats handle
> arbitrary precision, but do it rounding off values somewhat. The way the
> round off works is based on the (base 2) internal representation and so
> it never quite seems to make sense in decimal. It's possible on some
> machines/float formats to have "if (1.0 + 1.0 = 2.0)" evaluate to false.
>
> When I first started programming, I did a very simple custom
> accounting package using floats.... and had to go back and rewrite all
> of it.
>
> You will do better to use integers (no rounding at all) and consider
> the contents to be the number of pennies involved, not dollars.
>
> JT Kirkpatrick wrote:
> >
> > we have a database (linux, 6.4.2, new odbc, connections from msaccess /
> > win98) -- access doesn't fully appreciate the monetary type in postgres,
> so
> > we made all monetary fields FLOAT4's. weird stuff -- or maybe not to you
> > -- we insert 23516.69 into a float4 field directly in psql, and then
> select
> > it, and it shows 23516.7. this is not good for a field used for MONEY!!!
> > what are the limitations of float4? should i have used float8?
> >
> > thanks for any help you can offer. . .
> >
> > jt
>
> --
> Don Garrett dgarrett@acm.org
> BGB Consulting http://www.bgb-consulting.com/garrett
--
Don Garrett dgarrett@acm.org
BGB Consulting http://www.bgb-consulting.com/garrett
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] RE: [ADMIN] float4
@ 1999-06-29 08:28 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
1 sibling, 1 reply; 5+ messages in thread
From: Herouth Maoz @ 1999-06-29 08:28 UTC (permalink / raw)
To: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>; +Cc: 'pgsql-interfaces@hub.org' <pgsql-interfaces@hub.org>
At 23:53 +0300 on 28/06/1999, JT Kirkpatrick wrote:
> ouch. hard to think of dollars only when you are paying vendors,
> technicians, billing customers, etc. surely i won't have to make an int
> field for the dollars and an int field for the cents!!! the money field
> would work nicely if the odbc driver would handle it correctly!
>
> i forwarded this to the odbc list -- any idea why a money field can't be
> used from msaccess97 through the odbc??
Perhaps as a work around you should use an integer field, which is scaled
two places to the left? That is, if you have 300.52 dollars, you keep the
number 30052 in the field. Surely you can play around with the forms you
use, so that the real field will be hidden, and get its value from a
visible field multiplied by 100.
It becomes a problem if your sums can go beyond the 2^31 limit. But if you
have 6.4, you may use int8 and double your upper limit.
The problem with the money type is that queries on it return it with a
currency mark, which I'm sure is bad for odbc.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] RE: [ADMIN] float4
@ 1999-06-29 10:49 José Soares <jose@sferacarta.com>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 0 replies; 5+ messages in thread
From: José Soares @ 1999-06-29 10:49 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>; 'pgsql-interfaces@hub.org' <pgsql-interfaces@hub.org>
Herouth Maoz ha scritto:
> At 23:53 +0300 on 28/06/1999, JT Kirkpatrick wrote:
>
> > ouch. hard to think of dollars only when you are paying vendors,
> > technicians, billing customers, etc. surely i won't have to make an int
> > field for the dollars and an int field for the cents!!! the money field
> > would work nicely if the odbc driver would handle it correctly!
> >
> > i forwarded this to the odbc list -- any idea why a money field can't be
> > used from msaccess97 through the odbc??
>
> Perhaps as a work around you should use an integer field, which is scaled
> two places to the left? That is, if you have 300.52 dollars, you keep the
> number 30052 in the field. Surely you can play around with the forms you
> use, so that the real field will be hidden, and get its value from a
> visible field multiplied by 100.
>
> It becomes a problem if your sums can go beyond the 2^31 limit. But if you
> have 6.4, you may use int8 and double your upper limit.
>
> The problem with the money type is that queries on it return it with a
> currency mark, which I'm sure is bad for odbc.
>
> Herouth
>
> --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
I would like to reccomend to use the DECIMAL type instead of MONEY but I see
that PSQLODBC doesn't recognize
yet this type, maybe we can ask Byron to add this new feature to PSQLODBC ?
--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: [ADMIN] RE: [SQL] float4
@ 1999-07-01 12:38 JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
0 siblings, 0 replies; 5+ messages in thread
From: JT Kirkpatrick @ 1999-07-01 12:38 UTC (permalink / raw)
To: 'Bruce Momjian' <maillist@candle.pha.pa.us>; 'pgsql-interfaces@hub.org' <pgsql-interfaces@hub.org>; +Cc: 'Tom Lane' <tgl@sss.pgh.pa.us>; 'pgsql-admin@hub.org' <pgsql-admin@hub.org>
we turned OFF "use declare / fetch" on all clients odbc, and at the same
time upped buffers in postmaster to 512. we are no longer locking! i
think it was the declare/fetch thing. i have the front end, for the most
part, limiting users to a small number of records being transferred. i
watched debug as a user (only one on) selected ONE record. the server sat
there and processed many many many fetch 100's. i turned it off on her
machine, and it did only exactly what she asked it to do -- select one
record. now some users run large reports that pull from lots of records --
they have to have declare/fetch on or they overload client machine memory
and access97 quits responding (so ctrl alt del and end-task. . .). looks
fairly stable now, although i'd like to have the declare/fetch turned on
somehow by the front-end if the user selected a certain long report, and
not by the user who could / WILL forget to turn it off for normal use. . .
thanks for everyone's help!
jt / mpsllc.com
-----Original Message-----
From: Bruce Momjian [SMTP:maillist@candle.pha.pa.us]
Sent: Wednesday, June 30, 1999 11:29 PM
To: JT Kirkpatrick
Cc: 'Tom Lane'; 'pgsql-admin@hub.org'
Subject: Re: [ADMIN] RE: [SQL] float4
> ahh, so 6.5 has an answer! for our "cash and accounts payable" database
i
> converted the float4 field to int4, and altered the input mask on the
front
> end to show a decimal point, but not save it with the data. so now i am
> saving $35.99 as 3599. i had to alter all reports to divide by 100.
works
> fine. i'd really like to get it sincerely stable in 6.4.2 before
upgrading
> to 6.5, unless a reason for instability is solved in 6.5. we are still
> having problems with it locking up daily. . .
Very likely fixed in 6.5. People are reporting 6.5 as more stable.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-07-01 12:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-28 20:53 RE: [ADMIN] float4 JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
1999-06-28 21:06 ` Don Garrett <garrett@bgb-consulting.com>
1999-06-29 08:28 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-06-29 10:49 ` José Soares <jose@sferacarta.com>
1999-07-01 12:38 RE: [ADMIN] RE: [SQL] float4 JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox