Thread: numeric data type?

numeric data type?

From
Zlatko Matić
Date:
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
 

Re: numeric data type?

From
Zlatko Matić
Date:
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


Re: numeric data type?

From
Doug McNaught
Date:
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

Re: numeric data type?

From
Zlatko Matić
Date:
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


Re: numeric data type?

From
"John D. Burger"
Date:
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


Re: numeric data type?

From
Martijn van Oosterhout
Date:
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.

Attachment