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
