I have a database with two tables that store the CPF. In one of them the CPF is stored as numeric(11,0) and in the other the CPF is stored as character .
If there is any difference, which of the two data types takes up more storage space?
I understand that a CPF is not a numeric type, but a string of digits (there are CPFs that start with leading zeros), so a better abstraction of the data would be the use of a type character varying(11) .
And even though type character varying(11) takes up more space than type numeric(11,0) and the intent is to "optimize", yet it's not worth replacing. Optimizations of nature are almost always "early".
The character occupies slightly more, but is the correct one. CPF is a descriptive information that happens to be composed only of digits, one day it may not even be so. You do not have to do calculations with it, it does not quantify anything, so using a numeric type does not make the least sense.
See more .
This can be easily tested in the database:
SELECT pg_column_size(CHAR(11) '999999999'), pg_column_size(VARCHAR(11) '999999999'), pg_column_size(NUMERIC(9,0) '999999999');
See running SQL Fiddle .
Note that CHAR is what occupies most (which surprises me, this sounds like a bad thing in PostgreSQL), but it's semantically more correct. Think about it, if you know that this information has 11 characters, why would I create a type that size is variable? In normal databases this should be the most economical since it does not need any control metadata, I do not know why PostgreSQL does this. In another database you can give a different result. Would not it be better to use a general solution?
Curiously, using VARCHAR is a premature optimization.