what data type to store fixed size integer?

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

@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

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