Thread: numeric data type?

Thanks for answer, but in documentation I found that Numeric can be without
scale and precision defined:

"Specifying
NUMERICwithout any precision or scale creates a column in which numeric
values of any precision and scale can be stored, up to the implementation
limit on precision. A column of this kind will not coerce input values to
any particular scale, whereas numeric columns with a declared scale will
coerce input values to that scale. (The SQL standard requires a default
scale of 0, i.e., coercion to integer precision. We find this a bit useless.
If you're concerned about portability, always specify the precision and
scale explicitly.) "

Talking about float: "If you require exact storage and calculations (such as
for monetary amounts), use the numeric type instead. "

So, it seems that numeric without parameters (precision, scale) behave
similar to float, but is much exact. Am I right or I missunderstood?

Thanks,

Zlatko



----- Original Message -----
From: "Tony Caduto" <tony.caduto@amsoftwaredesign.com>
To: "Zlatko Matić" <zlatko.matic1@sb.t-com.hr>
Sent: Saturday, January 21, 2006 5:15 PM
Subject: Re: [GENERAL] numeric data type?


Zlatko Matić wrote:
> Is "numeric" data type good choice for a field that would store integer
> values in most cases, but sometimes decimal values as well?
> Thanks,
>  Zlatko
>
I think you would be better off with a float.
numeric has to have a scale set to it, while float4 or float8 does not.

--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql
http://www.amsoftwaredesign.com

Zlatko Matić <zlatko.matic1@sb.t-com.hr> writes:

> So, it seems that numeric without parameters (precision, scale) behave
> similar to float, but is much exact. Am I right or I missunderstood?

Right.  It's also considerably slower, since floating point
calculations can use the hardware.  Unless you're doing a huge number
of computations this may not be an issue.

-Doug

OK. Thanks for clarification.

----- Original Message -----
From: "Doug McNaught" <doug@mcnaught.org>
To: "Zlatko Matić" <zlatko.matic1@sb.t-com.hr>
Cc: <pgsql-general@postgresql.org>; "Tony Caduto"
<tony.caduto@amsoftwaredesign.com>
Sent: Sunday, January 22, 2006 2:39 PM
Subject: Re: [GENERAL] numeric data type?


Zlatko Matić <zlatko.matic1@sb.t-com.hr> writes:

> So, it seems that numeric without parameters (precision, scale) behave
> similar to float, but is much exact. Am I right or I missunderstood?

Right.  It's also considerably slower, since floating point
calculations can use the hardware.  Unless you're doing a huge number
of computations this may not be an issue.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

I have a (only vaguely) related question about NUMERICs.  I'm using
someone else's schema to copy data from their DB into mine.  They use
NUMERIC quite a bit, with scale 0, where I would use one of the integer
types.  My question is whether joining and matching on NUMERIC is
likely to be slower than, say, INTEGER.  Note that I'm never doing math
with these values, they are just IDs.

Thanks for any info provided!

- John D. Burger
   MITRE

On Mon, Jan 23, 2006 at 09:48:52AM -0500, John D. Burger wrote:
> I have a (only vaguely) related question about NUMERICs.  I'm using
> someone else's schema to copy data from their DB into mine.  They use
> NUMERIC quite a bit, with scale 0, where I would use one of the integer
> types.  My question is whether joining and matching on NUMERIC is
> likely to be slower than, say, INTEGER.  Note that I'm never doing math
> with these values, they are just IDs.

Yes, it's will be slower. Whether it's noticable... it depends on how
often you do it. The question is really, do you need to use numeric?
Will you be multiplying large numbers, do you expect decimals when you
divide, etc. Decide your answer to that before deciding about
performance issues.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.