Search code examples
databasepostgresqlsqldatatypesdigit

Use of datatype for 10-20 digit value - PostgreSQL

Im currently developing an application that needs to store a 10 to 20 digit value into the database.

My question is, what datatype should i need to be using? This digit is used as an primary key, and therefore the performance of the DB is important for my accplication. In Java i use this digit as and BigDecimal.

Solution

  • Quote from the manual:

    numeric: up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

    http://www.postgresql.org/docs/current/static/datatype-numeric.html

    131072 digits should cover your needs as far as I can tell.

    Edit:
    To answer the question about efficiency:

    The first and most important question is: what kind of data is stored in that column and how do you use it?

    • If it's a number then use numeric.
    • If it's not a number use a varchar.

    Never, ever store (real) numbers in character columns!

    If you need to sort by that column you won't be satifisfied with what you get if you use a character datatype (e.g. 2 will be sorted after 10)

    Coming back to the efficiency question. I assume this is mostly space efficiency you are concerned. You can calculate the space requirements for your values yourself.

    The storage requirement for the numeric data type is documented as well:

    The actual storage requirement is two bytes for each group of four decimal digits, plus five to eight bytes overhead

    So for 20 digits this would be a maximum of 10 bytes plus the five to eight bytes overhead. So max. 18 bytes.

    To store 20 digits in a varchar column you need 21 bytes.

    So from a space "efficiency" point of view numeric is slightly better. But that should never influence your decision, because the choice of datatypes should be driven by the requirements of the column's content.

    From a performance point of view I don't think there will be a big difference either.