21–30 of 47 posts
Zipcode is an integer? What about zips in New Jersey?
Earlier quoted context omitted.
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.
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.
This seems as good of place as any to ask novice-level questions about PostgreSQL. I teach students SQLite and one of the most massive pain points in transitioning to PgSQL is how the latter, in the `WHERE` clause, fails to recognize aliases in the `SELECT` clause, e.g. SELECT UPPER(name) AS bigname FROM people WHERE bigname = 'JOE'; https://stackoverflow.com/questions/38040631/postgresql-does... Apparently this is t…
Earlier quoted context omitted.
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.
This seems as good of place as any to ask novice-level questions about PostgreSQL. I teach students SQLite and one of the most massive pain points in transitioning to PgSQL is how the latter, in the `WHERE` clause, fails to recognize aliases in the `SELECT` clause, e.g. SELECT UPPER(name) AS bigname FROM people WHERE bigname = 'JOE'; https://stackoverflow.com/questions/38040631/postgresql-does... Apparently this is t…
SELECT bigname
FROM (
SELECT UPPER(name) AS bigname
FROM people
) x
WHERE bigname = 'JOE'
But this method can make large queries with lots of aliasing very difficult to read and lead to deep nesting. There is an alternative in MSSQL and (via a quick search) pgsql as well to keep the crazy nesting to a minimum:
-- MSSQL
SELECT bigname
FROM people
CROSS APPLY (SELECT UPPER(name) as bigname) x
WHERE bigname = 'JOE'
-- PGSQL (not 100% sure of syntax)
SELECT bigname
FROM people
LEFT JOIN LATERAL (SELECT UPPER(name) as bigname) x on true
WHERE bigname = 'JOE'
This seems as good of place as any to ask novice-level questions about PostgreSQL. I teach students SQLite and one of the most massive pain points in transitioning to PgSQL is how the latter, in the `WHERE` clause, fails to recognize aliases in the `SELECT` clause, e.g. SELECT UPPER(name) AS bigname FROM people WHERE bigname = 'JOE'; https://stackoverflow.com/questions/38040631/postgresql-does... Apparently this is t…
It's because the WHERE clause is evaluated before the SELECT. The reason that SQLite allows you to do that is because SQLite is much, much simpler than PgSQL, MySQL and SQL Server (all of which don't allow you to do this). http://tinman.cs.gsu.edu/~raj/sql/node22.html
Earlier quoted context omitted.
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,…
That's what I've always done..
Earlier quoted context omitted.
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.
Great idea, does what it should and can be extended to cover a lot more. Keep up the great work. First time looking at it I thought I was on a official Ubuntu site, what a font and color can do to a branding is fascinating.
Author here: out of curiosity, what would be your priorities for future exercises? Right now my list looks roughly like: - DDL - Advanced datatypes (JSON, arrays, etc) - GIS This order is partly informed by the current support of the site: adding DDL exercises is a relatively minor bit of technical work, and then it's just writing. Everything else involves schema changes (or, possibly, a second database).
Earlier quoted context omitted.
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,…