Hacker News Reader

Comment by genghisjahn | Hacker News Reader

Zipcode is an integer? What about zips in New Jersey?

grins for that I'll have to say that I'm from the UK and that situating the exercises in the US was probably an error :-)

As a general rule of thumb, if something isn't used for calculations, it probably shouldn't be a number.

That wouldn't generally be my approach. In my view if something is a number it should be typed as such.

That said, I recognise that there are pros and cons to this approach similar to those in strongly vs weakly typed PLs. My personal preference is for a strong schema.

There is a hidden assumption in your phrase "if something is a number". You imply that a zipcode is "obviously" a number. But it's not obvious. A zipcode is obviously a sequence of digits, but not everything that we represent as a sequence of digits is meaningfully treated as a number.

Leading zeros are one sign that zip codes are just digit strings, not numbers. Another (silly) example: if you REALLY have a number, then it doesn't matter what base you write it in. The number four can be written as 4 in base ten, or as 100 in base two. If you write a zip code in anything other than base ten, it's not really a zip code anymore.

Likewise, you can't add zip codes, nor subtract them, nor even meaningfully say that 11229 is more than 11228. You might as well use the letters A through J instead of the digits zero through nine, and suffer no serious inconvenience.

And indeed, some countries use something like zip codes, except that they have letters and numbers, yet I would hesitate to say that Canadian zip codes are so fundamentally different from US zip codes that they deserve to be stored under a different data type.

In conclusion, even you prefer a strong schema, a case can be made that zip codes should not be integers. Of course, that leaves open the question of what they should be, since most SQL databases have no built-in data type for digit sequences or anything similar. I have no answer to this and would personally just use integers anyway.

What's the issue with making them char(5) (or perhaps char(9) depending if you want to do zip+4)?

That's what I've always done..

it's not 'a case can be made' - it's strictly incorrect to store zip codes as integers

Ah, I should have been clearer in my initial response. I actually have no opinion on whether zip codes are a number, as I'm from the UK and don't really know how they work :-). I was more responding to the idea that any numeric value you don't do calculations on should be stored as a bare string. I would be happy enough with a string that had a constraint limiting the string to characters [0-9], though (again, assuming the data in question is limited to those chars).

On the whole, pgexercises punts on the data modelling aspect, as it's focused on teaching SQL instead - in some cases choosing deliberately bad schema design to make it easier to ask interesting questions. On reflection I'm not sure this was a good approach, but I'm far past the point where I have time/inclination to revisit it.

Ah; makes sense

So you'd create a different table for international addresses, or have 2 zip code fields or ...?

Zip codes aren't numbers, they are numeric strings.

You can store a NJ zip in an integer; you just have to be careful when printing it. Annoying, but if space is scarce, maybe worth it. (Though if space is too scarce for zipcodes, good luck storing anything else more interesting.)

Reply on news.ycombinator.com