13

I want to store a 15 digit number in a table.

In terms of lookup speed should I use bigint or varchar?

Additionally, if it's populated with millions of records will the different data types have any impact on storage?

Vivek S.
  • 19,945
  • 7
  • 68
  • 85
Dhanushka Amarakoon
  • 3,502
  • 5
  • 31
  • 43
  • 1
    **Never**, ever store numbers in a `varchar` column. **Never** –  Feb 12 '16 at 10:35
  • The following question gives an answer: http://stackoverflow.com/questions/2346920/sql-select-speed-int-vs-varchar As already said integers are faster. – Ghislain Rouvignac Feb 12 '16 at 14:46

2 Answers2

14

In terms of space, a bigint takes 8 bytes while a 15-character string will take up to 19 bytes (a 4 bytes header and up to another 15 bytes for the data), depending on its value. Also, generally speaking, equality checks should be slightly faster for numerical operations. Other than that, it widely depends on what you're intending to do with this data. If you intend to use numerical/mathematical operations or query according to ranges, you should use a bigint.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
4

You can store them as BIGINT as the comparison using the INT are faster when compared with varchar. I would also advise to create an index on the column if you are expecting millions of records to make the data retrieval faster.

You can also check this forum:

Indexing is fastest on integer types. Char types are probably stored underneath as integers (guessing.) VARCHARs are variable allocation and when you change the string length it may have to reallocate.

Generally integers are fastest, then single characters then CHAR[x] types and then VARCHARS.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • 5
    The `CHAR` vs. `VARCHAR` remark doesn't apply to Postgres; internally, they are identical, except that `CHAR` values are padded out with spaces, meaning that `VARCHAR` is actually faster in general. – Nick Barnes Feb 12 '16 at 14:35