Thread: what data type to store fixed size integer?
I wan to store a 10 digits integer in a column of table.
the data type does not need any arithmetic aperations (as of integers).
maybe its good to use bigint, but it is Postgres specific and is not part of SQL standards
what is the best data type (fastest) to store it? any help would be appreciated.
thanks in advance
Arash
On 04/10/2011 07:41 PM, Arash pajoohande wrote: > hi there > I wan to store a 10 digits integer in a column of table. > the data type does not need any arithmetic aperations (as of integers). > maybe its good to use bigint, but it is Postgres specific and is not > part of SQL standards > what is the best data type (fastest) to store it? any help would be > appreciated. int8 , which is just another name for bigint. I don't know what the SQL standard name for the data type is, but I'm pretty sure there is one. Anyone know the details? There's also NUMERIC , but it's not exactly fast or space-efficient. -- Craig Ringer
Craig Ringer <craig@postnewspapers.com.au> writes:
> On 04/10/2011 07:41 PM, Arash pajoohande wrote:
>> I wan to store a 10 digits integer in a column of table.
>> the data type does not need any arithmetic aperations (as of integers).
>> maybe its good to use bigint, but it is Postgres specific and is not
>> part of SQL standards
> int8 , which is just another name for bigint. I don't know what the SQL
> standard name for the data type is, but I'm pretty sure there is one.
> Anyone know the details?
SMALLINT and BIGINT are standardized type names in SQL:2003 and later.
regards, tom lane
>>>> the data type does not need any arithmetic operations (as of integers).
You arguably do not have a number but simply a string that looks like a number. Other examples are zip-codes and phone-numbers if you ignore symbols. Thus you should probably use an appropriately sized char/varchar.
Just something to consider; there is no hard and fast rule about this kind of thing. If you can think of any logical use of arithmetic operators, even if you do not need them now, you should use an integer.
Dave
>>>> the data type does not need any arithmetic operations (as of integers).
You arguably do not have a number but simply a string that looks like a number. Other examples are zip-codes and phone-numbers if you ignore symbols. Thus you should probably use an appropriately sized char/varchar.
Just something to consider; there is no hard and fast rule about this kind of thing. If you can think of any logical use of arithmetic operators, even if you do not need them now, you should use an integer.
thank you in advance
Arash
I probably do not give size and performance characteristics as much precedence as I should but using a varchar makes the model more flexible if you decide to change the identifier format. If you plan on simply using a serial that starts at 1-million then OK but if you are picking these numbers on some basis then varchar is something to consider.
Given the limited information supplied my mind just tends to jump to the fact that you are limiting input to numbers only where that may be an overly strict limitation. The other consideration is whether you fully control the assignment of those values.If you do not then the assumption is even more hazardous since the external format could already be character based.
That said it likely would be more benefical to use the smaller, restricted dataset if those limitations are acceptable.
From: Arash pajoohande [mailto:apajoohande@gmail.com]
Sent: Tuesday, April 12, 2011 1:32 AM
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] what data type to store fixed size integer?
@Dave
On Mon, Apr 11, 2011 at 9:18 PM, David Johnston <polobo@yahoo.com> wrote:
>>>> the data type does not need any arithmetic operations (as of integers).
You arguably do not have a number but simply a string that looks like a number. Other examples are zip-codes and phone-numbers if you ignore symbols. Thus you should probably use an appropriately sized char/varchar.
Do you think using char/varchar which in this case will take about 20 bytes for each entry is more proper than using bigint with only 4 bytes?
Just something to consider; there is no hard and fast rule about this kind of thing. If you can think of any logical use of arithmetic operators, even if you do not need them now, you should use an integer.
The data is some kind of identifiers. I don't think they will need any kind of arithmetic operators at all :)
thank you in advance
Arash