Storing integers with leading zeros 1

[pgosse]

Hi all. I recently had to create an online survey for one of our Vice Presidents, and one of the required fields is an alumni id. This is generally a nine digit number, but for older ids it can vary from 4-6 digits.

I need to be able to store older ids with leading zeros, but when I submit an older id, for example 000012345, what will be entered in the db is 12345.

This makes perfect sense, but in this case I actually need the leading zeros.

I don't really need to make changes until after the data has been collected, as I'm using PHP to output the database straight to an Excel spreadsheet, so I'll just pad the necessary ids at that point, but for future reference, how can I set it up such that integer fields can store the leading zeros?

Actually, I just found a bit of info in the docs that indicates that to do this I must actually use a character field to store the id, then convert to an integer if ever needed.

Is this the only way to accomplish this task?

Thanks much in advance,

Pablo

 

[stoolpigeon]

I store all 'id' type numbers as characters -- in all database systems that i use, not just postgres

 

[pgosse]

Thanks for the tip stoolpigeon. Any particular reason why?

Thanks in advance,
Pablo

 

[stoolpigeon]

For the reason you mention above. An identifier is not the same as a number. If I am counting widgets 0002 means 2 widgets and there is no difference.

If I am looking at zip codes 05201 is the zip code, 5201 is not.

In most cases you will be using these only to identify- you will not be performing mathematical operations on them, so there is no need to store them as an integer or some other number type. Sorting is usually not an issue- if they are all the same length.

phone numbers, account numbers, id numbers - or anything else like that - are not really numbers. They are names that use numerals instead of letters.

 

[newmangj]

If the data you are storing is numeric in nature I cannot think for the life of me why you would want to store it as a character string instead of an integer....

Why use 9 characters per record when a single integer would suffice. The fact that the data is stored without leading zeroes should be irrelevant, presumably you have some program extracting and formatting the data in a report, this program can "add" the leading zeroes at that stage, in C and PHP the printf function is perfectly capable of formatting an integer with a predefined field width with leading zeroes - this would satisfy your requirements.

In the meantime, by storing the data as a integer rather than a string the operations on that field (comparisons etc) are a lot faster and efficient.

Cheers - Gavin

 

[stoolpigeon]

what if the length is variable?

how do you pad a number if you don't know how long it was originally?

this matters for more than just reporting. you are conceivably adding a huge burden to developers on the application side.

What if the length of the 'number' exceeds storage limitations of standard numeric types?

using characters has many benefits that make it worthwhile. of course- always use whatever works best for your specific situation.

 

[newmangj]

stoolpigeon

The numbers we are talking about here (xip or postcodes, employee ids etc a re generally a fixed, known length)

Based on 30+ years of writing applications I would have thought the "load" on developers is minimal, whether they are dealing with numbers or strings the spacing on reports is still a matter they have to consider. Lets face it, if they are using printf functions to build the repotr they will still be using the width formatting specifier for both the strings or numbers.

It's unlikely there will be a number that exceeds the storage ability of PostgreSQLs variable types. After all a 32 bit integer can cope with numbers up to 4,294,967,295 and the 64 bit integer can hold up to 1,844,674,407,309,551,615. That is a lot of employees, its almost enough to hold Bill Gate$ net worth in dollars!

Cheers

 

[stoolpigeon]

Like I said, whatever works best for your situation.

I deal a lot with financial information. I handle identifiers that are quite large on a regular basis. In your largest example above the limit is 19 characters. I have account identifiers that exceed that.

And per the original question: "...but for older ids it can vary ..."

But it is silly to argue the whole thing. I think it is worth saying that in certain situations using characters instead of numeric types has discernible advantages that outweigh the cost in additional storage. That is all.

I cannot imagine if you have a lot of experience in programming that you would be willing to say - "never under any conditions store numeric identifiers as characters". I am sure that you would agree that there are always exceptions to any rule, and that the best systems are designed to meet unique demands rather than arbitrary rules.

 

[skiflyer]

I won't go so far as to say that you should never store numeric identifiers as characters... because I'm sure you're right, there are exceptions to any rule. But I can't honestly think of how that applies here.

The guy wants a 9-digit fixed length numeric id with leading zeros. Hands down, you store that as a number and pad it on the way out...

Take into account that all 9 digit id's can be stored in 4 bytes. Whereas each 9 digit character representation will be stored in 9 bytes. So by using digits over characters here you're using an extra 125% storage.

-Rob

 

[ceco]

I can think of a way with a view
create a view where the needed colomn is redesigned

lpad(text(field_name), 9, ''0'') AS field_name

and the selects are done to the view and thus this field will be always 9 digit leftpadded with 0s, and still internally saved as integer