Thread: simple limit of decimals

simple limit of decimals

From

pere roca

Date:

14 January 2009, 11:43:56

hi all,

I update a column based on a division over two real values. It causes a huge
number of decimals that I wanna to limit.

UPDATE point_pol SET taxa_record = ((SELECT ((CASE WHEN numtax>0 THEN
numtax/numreg ELSE 1 END))) where userid='hello'

taxa_record values are  real and with real I cannot limit the decimals.
I tried to apply a round in the sql sentence but complains that  round must
be applied to a single value each time.

how can I solve it? maybe using numeric? (but I have read numeric  reduces
performance...)
thanks!

Pere
--
View this message in context: http://www.nabble.com/simple-limit-of-decimals-tp21455215p21455215.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: simple limit of decimals

From

"A. Kretschmer"

Date:

14 January 2009, 11:59:57

In response to pere roca :
>
> hi all,
>
> I update a column based on a division over two real values. It causes a huge
> number of decimals that I wanna to limit.
>
> UPDATE point_pol SET taxa_record = ((SELECT ((CASE WHEN numtax>0 THEN
> numtax/numreg ELSE 1 END))) where userid='hello'
>
> taxa_record values are  real and with real I cannot limit the decimals.
> I tried to apply a round in the sql sentence but complains that  round must
> be applied to a single value each time.

Tray:

UPDATE point_pol SET taxa_record = round(((SELECT ((CASE WHEN numtax>0
THEN numtax/numreg ELSE 1 END))),2) ...


to round to 2 decimal places.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: simple limit of decimals

From

"Scott Marlowe"

Date:

14 January 2009, 12:14:36

On Wed, Jan 14, 2009 at 5:43 AM, pere roca <peroc79@gmail.com> wrote:
>
> hi all,
>
> I update a column based on a division over two real values. It causes a huge
> number of decimals that I wanna to limit.
>
> UPDATE point_pol SET taxa_record = ((SELECT ((CASE WHEN numtax>0 THEN
> numtax/numreg ELSE 1 END))) where userid='hello'
>
> taxa_record values are  real and with real I cannot limit the decimals.
> I tried to apply a round in the sql sentence but complains that  round must
> be applied to a single value each time.

Are you doing accounting with reals?  not a good idea due to rounding
issues, far better to use numeric(x,y) which will, incidentally, limit
your number of decimal places for you.

Re: simple limit of decimals

From

pere roca

Date:

14 January 2009, 12:17:41

  dear Andreas,

  thanks for the answer; yes, round should be the first, but it gives an
error:  Function round(real, integer) does not exists

following this mail
(http://archives.postgresql.org/pgsql-bugs/2003-01/msg00074.php) I created a
new function and now works!

 best regards,
 Pere

Andreas Kretschmer-4 wrote:
>
> In response to pere roca :
>>
>> hi all,
>>
>> I update a column based on a division over two real values. It causes a
>> huge
>> number of decimals that I wanna to limit.
>>
>> UPDATE point_pol SET taxa_record = ((SELECT ((CASE WHEN numtax>0 THEN
>> numtax/numreg ELSE 1 END))) where userid='hello'
>>
>> taxa_record values are  real and with real I cannot limit the decimals.
>> I tried to apply a round in the sql sentence but complains that  round
>> must
>> be applied to a single value each time.
>
> Tray:
>
> UPDATE point_pol SET taxa_record = round(((SELECT ((CASE WHEN numtax>0
> THEN numtax/numreg ELSE 1 END))),2) ...
>
>
> to round to 2 decimal places.
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>

--
View this message in context: http://www.nabble.com/simple-limit-of-decimals-tp21455215p21455726.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: simple limit of decimals

From

"A. Kretschmer"

Date:

14 January 2009, 12:59:50

In response to pere roca :
>
>   dear Andreas,
>
>   thanks for the answer; yes, round should be the first, but it gives an
> error:  Function round(real, integer) does not exists

UPDATE point_pol SET taxa_record = round(((SELECT ((CASE WHEN numtax>0
THEN numtax/numreg ELSE 1 END)))::numeric,2) ...

sorry, my fault, i forgot the CAST.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: simple limit of decimals

From

Emanuel Calvo Franco

Date:

14 January 2009, 13:51:14

2009/1/14 A. Kretschmer <andreas.kretschmer@schollglas.com>:
> In response to pere roca :
>>
>>   dear Andreas,
>>
>>   thanks for the answer; yes, round should be the first, but it gives an
>> error:  Function round(real, integer) does not exists
>
> UPDATE point_pol SET taxa_record = round(((SELECT ((CASE WHEN numtax>0
> THEN numtax/numreg ELSE 1 END)))::numeric,2) ...
>

An example of triki round:

postgres=# select round((9.88888::real)::numeric,2);
 round
-------
  9.89
(1 row)

postgres=#



> sorry, my fault, i forgot the CAST.
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
      Emanuel Calvo Franco
   ArPUG / AOSUG Member
 Postgresql Support & Admin