pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedbigint integers up to 19 digits.
6+ messages / 4 participants
[nested] [flat]
* bigint integers up to 19 digits.
@ 2010-02-04 18:15 Tory M Blue <tmblue@gmail.com>
2010-02-04 18:43 ` Re: bigint integers up to 19 digits. Craig James <craig_james@emolecules.com>
2010-02-04 18:47 ` Re: bigint integers up to 19 digits. Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tory M Blue @ 2010-02-04 18:15 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: bigint integers up to 19 digits.
2010-02-04 18:15 bigint integers up to 19 digits. Tory M Blue <tmblue@gmail.com>
@ 2010-02-04 18:43 ` Craig James <craig_james@emolecules.com>
2010-02-04 18:51 ` Re: bigint integers up to 19 digits. Tory M Blue <tmblue@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Craig James @ 2010-02-04 18:43 UTC (permalink / raw)
To: Tory M Blue <tmblue@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: bigint integers up to 19 digits.
2010-02-04 18:15 bigint integers up to 19 digits. Tory M Blue <tmblue@gmail.com>
2010-02-04 18:43 ` Re: bigint integers up to 19 digits. Craig James <craig_james@emolecules.com>
@ 2010-02-04 18:51 ` Tory M Blue <tmblue@gmail.com>
2010-02-04 19:01 ` Re: bigint integers up to 19 digits. Jochen Erwied <jochen@pgsql-performance.erwied.eu>
0 siblings, 1 reply; 6+ messages in thread
From: Tory M Blue @ 2010-02-04 18:51 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: bigint integers up to 19 digits.
2010-02-04 18:15 bigint integers up to 19 digits. Tory M Blue <tmblue@gmail.com>
2010-02-04 18:43 ` Re: bigint integers up to 19 digits. Craig James <craig_james@emolecules.com>
2010-02-04 18:51 ` Re: bigint integers up to 19 digits. Tory M Blue <tmblue@gmail.com>
@ 2010-02-04 19:01 ` Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-02-04 20:09 ` Re: bigint integers up to 19 digits. Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jochen Erwied @ 2010-02-04 19:01 UTC (permalink / raw)
To: Tory M Blue <tmblue@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: bigint integers up to 19 digits.
2010-02-04 18:15 bigint integers up to 19 digits. Tory M Blue <tmblue@gmail.com>
2010-02-04 18:43 ` Re: bigint integers up to 19 digits. Craig James <craig_james@emolecules.com>
2010-02-04 18:51 ` Re: bigint integers up to 19 digits. Tory M Blue <tmblue@gmail.com>
2010-02-04 19:01 ` Re: bigint integers up to 19 digits. Jochen Erwied <jochen@pgsql-performance.erwied.eu>
@ 2010-02-04 20:09 ` Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2010-02-04 20:09 UTC (permalink / raw)
To: Jochen Erwied <jochen@pgsql-performance.erwied.eu>; +Cc: Tory M Blue <tmblue@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: bigint integers up to 19 digits.
2010-02-04 18:15 bigint integers up to 19 digits. Tory M Blue <tmblue@gmail.com>
@ 2010-02-04 18:47 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2010-02-04 18:47 UTC (permalink / raw)
To: Tory M Blue <tmblue@gmail.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-02-04 20:09 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-04 18:15 bigint integers up to 19 digits. Tory M Blue <tmblue@gmail.com>
2010-02-04 18:43 ` Craig James <craig_james@emolecules.com>
2010-02-04 18:51 ` Tory M Blue <tmblue@gmail.com>
2010-02-04 19:01 ` Jochen Erwied <jochen@pgsql-performance.erwied.eu>
2010-02-04 20:09 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-02-04 18:47 ` Alvaro Herrera <alvherre@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox