[PERFORM] 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] bigint integers up to 19 digits.
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? Craig James -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] bigint integers up to 19 digits.
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] bigint integers up to 19 digits.
On Thu, Feb 4, 2010 at 10:43 AM, Craig James craig_ja...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] bigint integers up to 19 digits.
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: joc...@erwied.eu +49-208-38800-18, FAX: -19 Sauerbruchstr. 17 | work: j...@mbs-software.de +49-2151-7294-24, FAX: -50 D-45470 Muelheim | mobile: jochen.erw...@vodafone.de +49-173-5404164 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] bigint integers up to 19 digits.
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 1 in the current implementation -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance