pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
fields with float[48] type question
3+ messages / 2 participants
[nested] [flat]

* fields with float[48] type question
@ 1999-06-09 15:45 Sergey S. Rakitin <ssr@visti.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Sergey S. Rakitin @ 1999-06-09 15:45 UTC (permalink / raw)
  To: pgsql-sql


		Hi,

	I have two pc's with PostgreSQL 6.4.2r and 6.5beta2
	and some troubles with both of them.

	Well, here's an example:
	I create some simple table with float field (called field_one), insert
	value 100 in it. Then I need to increase/decrease it number by 0.06 on
every step
	many times (from trigger/SPI or simple shell script contains just _21_
(or less)
	lines like this: 'update <class_name> set field_one = field_one [-+]
0.06')...
	Here'we go. If I put _22+_ lines I got very interesting results. For
example:
	100 - 22 times by 0.06 == 98.6799999999999 instead of 98.68... And it's
not
	the end of story...


		Can someone explain me such behaviour?..

--
 Sergey S. Rakitin        ElVisti
 SSR2-RIPE          Information Center



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] fields with float[48] type question
@ 1999-06-10 08:56 Stuart Rison <stuart@ludwig.ucl.ac.uk>
  1999-06-10 12:24 ` Re: [SQL] fields with float[48] type question Sergey S. Rakitin <ssr@visti.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Stuart Rison @ 1999-06-10 08:56 UTC (permalink / raw)
  To: pgsql-sql; ssr@visti.net

Hi Serguey,

I can't explain the behaviour -although I've had similar "trouble" with
AGGREGATES on floats.

I potential work-around that could to work with your example though is:

CREATE SEQUENCE test_seq INCREMENT -6 START 10000 MAXVALUE 10000 MINVALUE 0;

CREATE FUNCTION get_nextval() RETURNS float AS
'SELECT nextval(\'test_seq\')/100.0' LANGUAGE 'sql';

then...

SELECT get_nextval(); [first time]

NOTICE:  test_seq.nextval: sequence was re-created
get_nextval
-----------
        100
(1 row)

SELECT get_nextval(); [22nd time]

get_nextval
-----------
      98.68
(1 row)

problem is you have to multiply your starting value and the increment by
whatever it takes to 'get rid' of decimal places.  That fine if your
increment is 0.06 but if it's 0.0000001225357 it becomes rather more silly!

anyway HTH,

regards,

Stuart

>	I have two pc's with PostgreSQL 6.4.2r and 6.5beta2
>	and some troubles with both of them.
>
>	Well, here's an example:
>	I create some simple table with float field (called field_one), insert
>	value 100 in it. Then I need to increase/decrease it number by 0.06 on
>every step
>	many times (from trigger/SPI or simple shell script contains just _21_
>(or less)
>	lines like this: 'update <class_name> set field_one = field_one [-+]
>0.06')...
>	Here'we go. If I put _22+_ lines I got very interesting results. For
>example:
>	100 - 22 times by 0.06 == 98.6799999999999 instead of 98.68... And it's
>not
>	the end of story...
>
>
>		Can someone explain me such behaviour?..
+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] fields with float[48] type question
  1999-06-10 08:56 Re: [SQL] fields with float[48] type question Stuart Rison <stuart@ludwig.ucl.ac.uk>
@ 1999-06-10 12:24 ` Sergey S. Rakitin <ssr@visti.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Sergey S. Rakitin @ 1999-06-10 12:24 UTC (permalink / raw)
  To: Stuart Rison <stuart@ludwig.ucl.ac.uk>; +Cc: pgsql-sql

Stuart Rison wrote:

		Hi Stuart,

Thanks a lot.

Well, how I told before one of my PgSQL versions is 6.5 with type
'numeric' of data.
I'm trying to play with it right now. It seems to be very usefull for
me! I'm getting true value in my example above.
But if increment is [*].0000001225357 then scale truncated to 6 digits
(even if the field created with scale > 6)... :(
My question is how to get work right with numeric operators for
'numeric' and float types? There's such functions as float8_numeric()
(returns numeric), but I can't get 7th digit of scale anyway.


> I can't explain the behaviour -although I've had similar "trouble" with
> AGGREGATES on floats.
> 
> I potential work-around that could to work with your example though is:
> 
> CREATE SEQUENCE test_seq INCREMENT -6 START 10000 MAXVALUE 10000 MINVALUE 0;
> 
> CREATE FUNCTION get_nextval() RETURNS float AS
> 'SELECT nextval(\'test_seq\')/100.0' LANGUAGE 'sql';
> 
> then...
> 
> SELECT get_nextval(); [first time]
> 
> NOTICE:  test_seq.nextval: sequence was re-created
> get_nextval
> -----------
>         100
> (1 row)
> 
> SELECT get_nextval(); [22nd time]
> 
> get_nextval
> -----------
>       98.68
> (1 row)
> 
> problem is you have to multiply your starting value and the increment by
> whatever it takes to 'get rid' of decimal places.  That fine if your
> increment is 0.06 but if it's 0.0000001225357 it becomes rather more silly!
> 
> anyway HTH,
> 
> regards,
> 
> Stuart
> 
> >       I have two pc's with PostgreSQL 6.4.2r and 6.5beta2
> >       and some troubles with both of them.
> >
> >       Well, here's an example:
> >       I create some simple table with float field (called field_one), insert
> >       value 100 in it. Then I need to increase/decrease it number by 0.06 on
> >every step
> >       many times (from trigger/SPI or simple shell script contains just _21_
> >(or less)
> >       lines like this: 'update <class_name> set field_one = field_one [-+]
> >0.06')...
> >       Here'we go. If I put _22+_ lines I got very interesting results. For
> >example:
> >       100 - 22 times by 0.06 == 98.6799999999999 instead of 98.68... And it's
> >not
> >       the end of story...
> >
> >
> >               Can someone explain me such behaviour?..


--
 Sergey S. Rakitin        ElVisti
 SSR2-RIPE          Information Center



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-06-10 12:24 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-09 15:45 fields with float[48] type question Sergey S. Rakitin <ssr@visti.net>
1999-06-10 08:56 Re: [SQL] fields with float[48] type question Stuart Rison <stuart@ludwig.ucl.ac.uk>
1999-06-10 12:24 ` Sergey S. Rakitin <ssr@visti.net>

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