121

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?

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

EvilTeach
  • 28,120
  • 21
  • 85
  • 141
  • 2
    According to http://en.wikipedia.org/wiki/List_of_postal_codes , the longest is 12 characters, if you are storing the '-', else 11 – Neil McGuigan Nov 07 '13 at 19:07
  • Useful link, however it's accuracy may be a bit out. EG it lists Australian postcodes as being 7 characters, when in fact they're 4. Ref: http://en.wikipedia.org/wiki/Postcodes_in_Australia and the postcode list available at http://www1.auspost.com.au/postcodes/. – rossp Nov 28 '08 at 04:29
  • re: my previous comment - that doesn't mean this list isn't useful as a guide. Assuming the list errs on the side of longer postcodes, the longest length is 9 characters so 16 characters or thereabouts should give you plenty of room to breathe. – rossp Nov 28 '08 at 04:30
  • Also country list's a bit short. I'm sure there's more countries on the planet than those listed... – Robert Koritnik Oct 11 '12 at 15:12
  • @CMS: You might want to update the link to [this wikipedia page](http://en.wikipedia.org/wiki/List_of_postal_codes), it looks like to be more detailed. – Vajk Hermecz Mar 26 '15 at 14:05
  • The link in the original answer doesn't work. You can use the following links: [International Postal Codes](http://www1.barnesandnoble.com/help/cds2.asp?pid=8134) and [Wikipedia](https://en.wikipedia.org/wiki/Postal_code) – Mustafa Jul 23 '15 at 10:21
  • @Mustafa your barnes & noble link doesn't work (and, strange you'd link there for postal code info...) – Jon L. May 07 '19 at 18:37

8 Answers8

65

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

strager
  • 88,763
  • 26
  • 134
  • 176
  • 10
    Good link. Even allowing for the punctuation in US ZIP+4, 10 characters would be enough for any country as far as I could tell. – Jonathan Leffler Nov 28 '08 at 05:36
  • Based on this link, from the page linked above, I'd go with 18 to accommodate countries like Chile: https://en.wikipedia.org/wiki/List_of_postal_codes – mopo922 Jan 13 '16 at 04:04
  • 9
    Chile is 7 characters. The web page you referenced simply shows punctuation variance. – EvilTeach Jan 20 '16 at 15:47
25

As already raised by @neil-mcguigan, wikipedia has a decent page on the topic. Based on that 12 characters should do it: http://en.wikipedia.org/wiki/List_of_postal_codes

The wikipedia article lists ~254 countries, which is pretty good regarding UPU (Universal Postal Union) has 192 member countries.

Vajk Hermecz
  • 5,413
  • 2
  • 34
  • 25
  • 2
    Note that Montserrat is only 8 chars, 1110-1350 denotes a range. https://discovermni.com/about-montserrat/montserrat-post-codes/ – Vajk Hermecz Mar 06 '18 at 22:30
  • Possibly Wikipedia needs editing, since the similar looking postal code for Malta has a generic one like "AAA NNNN". I would not mind to have even 15 characters because it could only be less problem later if we have to adjust column length, also with right use of data types, it should not take all 15 characters anyway (possibly varchar or nvarchar or like?). – Manohar Reddy Poreddy Mar 08 '18 at 03:09
13

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 4
    Well, assuming we are coding in something silly like Pro*C, having the field large enough for growth means the code won't need to be touched should the usage increase. – EvilTeach Nov 29 '08 at 18:41
  • Yes breaking the us zip code into 5 and 4 digits can make sense, depending on what you plan on using it for. For example, if you are doing some sort of address matching, you might want to match on the zip5 first, and resolve ambigueous situations with the zip 9. It also helps to use a country code – EvilTeach Nov 29 '08 at 18:43
4

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

Stephan Eggermont
  • 15,847
  • 1
  • 38
  • 65
  • Interesting. A different viewpoint simply downvoted with no reason why. +1 – EvilTeach May 25 '11 at 02:05
  • A postal code will typically reference a block on one side of the street. To find a broader region, you would select the first half of the postal code. Having this information in a separate table really won't help anything and would be more complicated to maintain. – RevNoah Dec 02 '13 at 17:08
  • 6
    @EvilTeach: I bet it was downvoted because its off-topic. Does it tell you how large a column should be to store every possible postal code in the world? No. – wmax Oct 12 '16 at 11:51
3

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.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • I agree. Using a VARCHAR2 field the reality is for a field like postcode it really doesnt matter. Slightly too big is better than annoying one customer because they cant input their details. – Toby Allen Nov 28 '08 at 07:42
  • And varchars are handy since databases (at least DB2) may optimize storage of them, so as to not waste storage space. – paxdiablo Nov 28 '08 at 07:49
  • 1
    one would point out that sorting by country and postal code will result in cheaper postal rates in some places. – EvilTeach Nov 28 '08 at 21:25
  • @EvilTeach, how so? Surely you have to send stuff from A to B, I can't immediately see how sort order from a database affects this (maybe that's just me, of course). – paxdiablo Nov 28 '08 at 22:54
  • 10
    Disgaree. Sometime down the line you'll decide you'll need to validate the addresses in your database (eg to correct typographical and data entry errors) and that's when you'll find the benefit of properly constructing your data model rather than just shoving everything in buckets. – Gary Myers Nov 29 '08 at 21:43
  • @Igor: Then that would be the missing reason to do it - but that reason doesn't exist NOW, so you're wasting time and money catering for the requirement. – paxdiablo Nov 30 '08 at 00:40
  • 1
    @Pax If you hand over bulk mail to the Royal Mail presorted by the head district (first letter/two letters) of the postcode, then you can have it delivered by MailSort, which is cheaper than regular second class mail. That's just one example. – Richard Gadsden Jul 15 '09 at 17:03
  • I agree with Gary. I just integrated a payment processor which returns error if you pass it a user postcode longer than 10 characters. Using the links provided above, as far as I can see American Samoa would be the only one to exceed 10 characters, but some further googling tells me that the whole island uses U.S. zip code 96799 so I think 10 will be a good maximum, especially for my case where I don't want errors from this payment processor. If I'd put this constraint in from the start I wouldn't have to be doing clean up duty now. – Phil R Feb 04 '15 at 19:32
  • To which I would just say, YAGNI. If you _know_ you'll need something, cater for it. But that wasn't the case here. Catering for something that's never needed is sunk cost, a waste. – paxdiablo Feb 04 '15 at 22:14
2

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

tegbains
  • 597
  • 3
  • 14
  • 3
    Canadian postal codes have a blank in the middle "ANA NAN" Thats 7 characters. – EvilTeach Nov 28 '08 at 04:25
  • 1
    But the space is always in the middle so you don't need to store it. – Graeme Perrow Nov 28 '08 at 04:26
  • @EvilTeach - yes, but you can expect data to be normalized before being stored – ysth Nov 28 '08 at 04:26
  • The space could be used to identify it from other postal code types. It'd be faster to store it in its presentation form so it is consistent with all other postal codes in the table, too. No need to regexp to "denormalize" the code. – strager Nov 28 '08 at 06:16
  • 1
    The space doesn't seem to be a part of the data: "Note: Canadian postal codes are always formatted in the same sequence: alphabetic character / numeral / alpha /numeral / alpha / numeral (e.g. K1A0B1)." That's from the Canada Post website. – tegbains Nov 29 '08 at 06:57
  • @strager: I think it would be better to base the Postal Code type on the Country instead of what is entered by the user as the Postal Code. You could use a regex based on the Country to verify the Postal Code entry by the user. – tegbains Nov 29 '08 at 06:59
  • 2
    I don't think omitting the space has anything to do with 'normalisation'. It's merely a display issue. Like dashes in account numbers. I wouldn't store it, and I wouldn't rely on it to identify Canadian postcodes in preference to a CountryCode (int) field that can be indexed. Seperating Data and Presentation layer is the right way to do it. – Sam Nov 17 '11 at 03:50
  • 2
    Canada Post prefers the space in the postal code when addressing envelopes. It's best to store it with the space and handle validation on entry. – RevNoah Dec 02 '13 at 17:04
  • @RevNoah I was about to post a counter to your point and then realized I actually agree with your point. LOL Guess it has been a longer day than I realized. – Andrew Steitz May 03 '16 at 19:56
2

UK have published standards: UK Government Data Standards Catalogue

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

The UK postal code length is:

Minimum 6 and Maximum 8 characters
PodTech.io
  • 4,874
  • 41
  • 24
1

If you want to integrate postal codes in database then geonames database is best to use. Even though it is tough to use and understand but it is the largest geographical database available freely to users like us.

All the other such database are more or less likely have same data and structure. They just remove some extra/redundant information from database. If you are just doing it for low load systems use their free services the limits are attractive and provides more easy interface using json and ajax. You can view the limits here

For your information varchar(20) is sufficient for storing postal codes

Jay Kapasi
  • 81
  • 1
  • 3