3 ms·

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

AlisdairO 9y ago
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 :-)
- Chickenosaurus 9y ago
  As a general rule of thumb, if something isn't used for calculations, it probably shouldn't be a number.
  - AlisdairO 9y ago
    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.
    - marvy 9y ago
      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.
      - Danihan 9y ago
        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..
      - wehere111 9y ago
        it's not 'a case can be made' - it's strictly incorrect to store zip codes as integers
      - AlisdairO 9y ago
        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.
        marvy 9y ago
        Ah; makes sense
    - detaro 9y ago
      So you'd create a different table for international addresses, or have 2 zip code fields or ...?
    - Danihan 9y ago
      Zip codes aren't numbers, they are numeric strings.
marvy 9y ago
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.)

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 :-)

Chickenosaurus 9y ago
As a general rule of thumb, if something isn't used for calculations, it probably shouldn't be a number.
- AlisdairO 9y ago
  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.
  - marvy 9y ago
    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.
    - Danihan 9y ago
      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..
    - wehere111 9y ago
      it's not 'a case can be made' - it's strictly incorrect to store zip codes as integers
    - AlisdairO 9y ago
      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.
      - marvy 9y ago
        Ah; makes sense
  - detaro 9y ago
    So you'd create a different table for international addresses, or have 2 zip code fields or ...?
  - Danihan 9y ago
    Zip codes aren't numbers, they are numeric strings.

marvy 9y ago

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.)