Thread: bigint integers up to 19 digits.
Greetings, I have a column that is a bigint that needs to store integers up to 19 digits long. For the most part this works but we sometimes have numbers that are greater than 9223372036854775807. I was thinking of changing this to a real or double precision field, but read in the docs that the value stored is not always the value inserted. From the docs " Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and printing back out a value may show slight discrepancies". Is it known what level of precision is provided by the double data type. My number will always be 19 digits long and always an integer. I looked into the numeric data type, but the docs say that it can be slow. Any feedback would be appreciated. Thanks Tory
Tory M Blue wrote: > I have a column that is a bigint that needs to store integers up to 19 > digits long. For the most part this works but we sometimes have > numbers that are greater than 9223372036854775807. > ... > I was thinking of changing this to a real or double precision field, > but read in the docs that the value stored is not always the value > inserted... They're actually less precise than the same size of integer. Real/double datatypes trade more range for less precision inthe same number of bytes. > My number will always be 19 digits long and always an integer. > I looked into the numeric data type, but the docs say that it can be slow. If it's *always* going to be 19 digits, couldn't you make it a text or char field? You didn't say if this is really a number. Do you do arithmetic with it? Sort it numerically? Or is it just a long identifier that happens to only used digits? Craig James
Tory M Blue escribió: > I looked into the numeric data type, but the docs say that it can be slow. It is slower than values that fit in a single CPU register, sure. Is it slow enough that you can't use it? That's a different question. I'd give it a try -- maybe it's not all that slow. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Thu, Feb 4, 2010 at 10:43 AM, Craig James <craig_james@emolecules.com> wrote: > Tory M Blue wrote: >> >> I have a column that is a bigint that needs to store integers up to 19 >> digits long. For the most part this works but we sometimes have >> numbers that are greater than 9223372036854775807. >> ... >> I was thinking of changing this to a real or double precision field, >> but read in the docs that the value stored is not always the value >> inserted... > > They're actually less precise than the same size of integer. Real/double > datatypes trade more range for less precision in the same number of bytes. > >> My number will always be 19 digits long and always an integer. >> I looked into the numeric data type, but the docs say that it can be slow. > > If it's *always* going to be 19 digits, couldn't you make it a text or char > field? You didn't say if this is really a number. Do you do arithmetic > with it? Sort it numerically? Or is it just a long identifier that happens > to only used digits? it is an identifier and is always a number and is used in grouping and querying. I thought I would lose performance if it is text vs an integer/double field. Tory
Thursday, February 4, 2010, 7:51:37 PM you wrote: > it is an identifier and is always a number and is used in grouping and > querying. I thought I would lose performance if it is text vs an > integer/double field. Maybe using 'numeric(19)' instead of bigint is an alternative. I actually don't know how these numbers are stored internally (some kind of BCD, or as base-100?), but IMHO they should be faster than strings, although not as fast as 'native' types. -- Jochen Erwied | home: jochen@erwied.eu +49-208-38800-18, FAX: -19 Sauerbruchstr. 17 | work: joe@mbs-software.de +49-2151-7294-24, FAX: -50 D-45470 Muelheim | mobile: jochen.erwied@vodafone.de +49-173-5404164
Jochen Erwied escribió: > Maybe using 'numeric(19)' instead of bigint is an alternative. I actually > don't know how these numbers are stored internally (some kind of BCD, or as > base-100?), but IMHO they should be faster than strings, although not as > fast as 'native' types. base 10000 in the current implementation -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support