I need to store zip codes in a database. How big is the column?

Question

I expect the column to be VARCHAR2 in my Oracle database.

US Zips: 9.

Canadian - 7.

I think 32 characters would be a reasonable upper limit

What am I missing?

[EDIT] TIL: 12 - A reasonable answer to the question Thanks to everyone who contributed.

Answer 1

When shooting Wikipedia Postal Codes pages, there should be more than 32 characters. I would say that even 16 characters are good.

Answer 2

As @ neil-mcguigan already wrote, Wikipedia has a decent page on this topic. Based on this, 12 characters should do this: http://en.wikipedia.org/wiki/List_of_postal_codes

The Wikipedia article lists ~ 254 countries, which has 192 member countries pretty well regarding the UPU (Universal Postal Union) .

Answer 3

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

If the original version of your application will support the addresses of the USA and Canada (which I deduced from the fact that you specified these sizes in your question), I would declare this field as VARCHAR2 (9) (or VARCHAR2 (10) if you are going to store hyphen in ZIP + 4 fields). Even looking at messages made by other people in the postal codes by country, VARCHAR2 (9) or VARCHAR2 (10) will be sufficient for most, if not all other countries.

Down the line, you can always add an ALTER column to increase the length if necessary. But, as a rule, it’s difficult to prevent someone, somewhere from making a decision about getting “creative” and 50 other characters in the VARCHAR2 field (50) for one reason or another (i.e., because they want a different line on transport label). You should also deal with borderline cases (will there be every application that displays a 50 character zip descriptor?). And with the fact that when clients retrieve data from a database, they usually allocate memory based on the maximum size of the data to be retrieved, rather than the actual length of a given row. This is probably not so important in this particular case, but 40 bytes per line can be a decent amount of RAM for some situations.

As an aside, you can also consider storing (at least for US addresses) the zip code and the +4 extension separately. In general, it’s useful to be able to generate reports by geographic region, and you often want to put everything in a zip code together rather than break it down by +4. At this point, it is useful not to try SUBSTR to output the first 5 characters for the zip code.

Answer 4

What you are missing is the reason that you need a postal code that needs to be specially processed.

If you really don't need WORK with the postal code, I would suggest not to worry about it. By work, I mean special processing, and not just for printing address labels, etc.

Just create three or four address fields VARCHAR2 (50) [for example] and let the user enter what they want.

Do you really need to group your orders or transactions by zip code? I think not, because in different countries there are different schemes for this area.

Answer 5

Normalization? Postal codes can be used more than once and can be associated with street names or city names. Separate table (s).

Answer 6

Canadian postal codes only 6 characters, in the form of letters and numbers (LNLNLN)

Answer 7

If you want to integrate the postal codes into a database, it is best to use the geonames database. Although it’s hard to use and understand, it’s the largest geographic database available to users like us.

All other such databases have more or less likely the same data and structure. They simply remove the extra / redundant information from the database. If you just do it for systems with low load, use their free services, the limits are attractive and provide a more convenient interface using json and ajax. You can view the restrictions here.

For your information, varchar (20) is enough to store zip codes

Answer 8

UK standards published: UK Government Data Standards Directory

Max 35 characters per line 

International postal address:

 Minimum of 2 lines and maximum of 5 lines for the postal delivery point details, plus 1 line for country and 1 line for postcode/zip code 

UK Postal Code Length:

 Minimum 6 and Maximum 8 characters