I need to store postal codes in a database. How big should the column be?

Question

I expect the column to be a VARCHAR2, in my Oracle Database.

US Zips are 9.

Canadian is 7.

I am thinking 32 characters would be reasonable upper limit

What am I missing?

Answer 1

Canadian Postal Codes are only 6 characters, in the form of letter's and numbers (LNLNLN)

Answer 2

Skimming through Wikipedia's Postal Codes page, 32 characters should be more than enough. I would say even 16 characters is good.

Answer 3

Here you can find a list of International Postal Codes formats.

Answer 4

What you're missing is a reason why you need the postal code to be handled specially.

If you don't really need to WORK with a postal code, I would suggest not worrying about it. By work, I mean do special processing for rather than just use to print address labels and so on.

Simply create three or four address fields of VARCHAR2(50) [for example] and let the user input whatever they want.

Do you really need to group your orders or transactions by postcode? I think not, since different countries have vastly different schemes for this field.

Answer 5

Normalization? Postal codes might be used more than once, and might be related to street names or town names. Separate table(s).

Answer 6

Why would you declare a field size larger than the actual data you are expecting to store in it?

If the initial version of your application is going to support US and Canadian addresses (which I'm inferring from the fact that you call out those sizes in your question), I'd declare the field as VARCHAR2(9) (or VARCHAR2(10) if you intend to store the hyphen in ZIP+4 fields). Even looking at the posts others have made to postal codes across countries, VARCHAR2(9) or VARCHAR2(10) would be sufficient for the most if not all other countries.

Down the line, you can always ALTER the column to increase the length should the need arise. But it is generally hard to prevent someone, somewhere from deciding to get "creative" and stuff 50 characters into a VARCHAR2(50) field for one reason or another (i.e. because they want another line on a shipping label). You also have to deal with testing the boundary cases (will every application that displays a ZIP handle 50 characters?). And with the fact that when clients are retrieving data from the database, they are generally allocating memory based on the maximum size of the data that will be fetched, not the actual length of a given row. Probably not a huge deal in this specific case, but 40 bytes per row could be a decent chunk of RAM for some situations.

As an aside, you might also consider storing (at least for US addresses) the ZIP code and the +4 extension separately. It is generally useful to be able to generate reports by geographical region, and you may frequently want to put everything in a ZIP code together rather than breaking it down by the +4 extension. At that point, it's useful to not have to try to SUBSTR out the first 5 characters for the ZIP code.