CODEWITHSUNDEEP
postgresqltypesvarcharbigint
Dhanushka Amarakoon
Dhanushka Amarakoon

Reputation: 3762

PostgreSql storing a value as integer vs varchar

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?

Upvotes: 13

Views: 9276

Answers (2)

Mureinik
Mureinik

Reputation: 311188

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.

Upvotes: 14

Rahul Tripathi
Rahul Tripathi

Reputation: 172428

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.

Upvotes: 4

Related Questions