Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL Exercises

pgexercises.com

Post reply on HN
Print view View original

31–40 of 47 posts

Re: PostgreSQL Exercises

#31

Post by AlisdairO »

marvy wrote: post #22
AlisdairO wrote: post #20

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,…

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.

Re: PostgreSQL Exercises

#32

Post by gigatexal »

AlisdairO wrote: post #14
tekkk wrote: post #4

Very impressive! Works well, nice UI, informative. After doing a couple exercises what I don't like is the naming convention. Something like "montlymaintenance" or "recommendedby" are just hard to read and type and I'd much rather use snake-case and/or shorter names. Eg. "monthly_maintenance" or "maintenance_per_month" or just "maintenance" if it's explicit enough that it's always per month. Also to me using shorthan…

Author here - thanks a lot for the feedback, it's appreciated. You're right on the naming convention, it's very haphazard. I'll raise an issue on Github to revisit that - although it may take me quite a while to get to it as I have a two week old to look after right now :-).

Kudos on the new budding programmer you got there. ;-)

Re: PostgreSQL Exercises

#33

Post by gigatexal »

danso wrote: post #19

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…

in SQLSERVER you could build your selects (using unique names or aliases) and then wrap it all in a common table expression like so:

; with cte as ( select a as A, b as B, c as C from dbo.table1 tbl1 join dbo.table2 tbl2 on tbl2.id = tbl1.id) select A, B, C from cte

Re: PostgreSQL Exercises

#34

Post by JCSato »

AlisdairO wrote: post #14
tekkk wrote: post #4

Very impressive! Works well, nice UI, informative. After doing a couple exercises what I don't like is the naming convention. Something like "montlymaintenance" or "recommendedby" are just hard to read and type and I'd much rather use snake-case and/or shorter names. Eg. "monthly_maintenance" or "maintenance_per_month" or just "maintenance" if it's explicit enough that it's always per month. Also to me using shorthan…

Author here - thanks a lot for the feedback, it's appreciated. You're right on the naming convention, it's very haphazard. I'll raise an issue on Github to revisit that - although it may take me quite a while to get to it as I have a two week old to look after right now :-).

Congratulations!

Re: PostgreSQL Exercises

#35

Post by stats_n_trends »

danso wrote: post #19

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…

I’m always saddened when I see this feature lacking in so many databases. Especially as Teradata supports this feature so i get to use it at my day job but not on any open source projects.

Re: PostgreSQL Exercises

#36

Post by stats_n_trends »

hungerstrike wrote: post #23
danso wrote: post #19

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

As a counter point teradata (a ridiculously expensive closed source but fairly complex database) supports this feature. So it is definitely possible for this feature to be supported.

Re: PostgreSQL Exercises

#37

Post by nik736 »

2T1Qka0rEiPr wrote: post #3
nik736 wrote: post #2

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.

Well, it is using the Ubuntu font! https://pgexercises.com/css/site.css

Yes, I know, that's what I am talking about. ;-)

That's exactly what good branding looks like!

Re: PostgreSQL Exercises

#38

Post by tux1968 »

hungerstrike wrote: post #23
danso wrote: post #19

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

You would think that an initial pass of SELECT could be made only to note any aliases. It would be a much appreciated nod to usability.

Re: PostgreSQL Exercises

#39

Post by combatentropy »

hungerstrike wrote: post #23
danso wrote: post #19

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

Yes. I wish SQL select-statements put the select-clause last:

   from people
   where name = 'Joe'
   select upper(name) as bigname
1. It would mirror the other clauses (insert, update, and delete) which all begin with the table name.

2. It would be easier on my head. I skip the select-clause anyway when reading SQL, then come back up to it at the end, when I know the columns' source.

3. It would show that using column aliases in the where-clause would fail (unless the database did some kind of two-pass processing).

Re: PostgreSQL Exercises

#40

Post by marvy »

AlisdairO wrote: post #31
marvy wrote: post #22

Earlier quoted context omitted.

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,…

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, assumi…

Ah; makes sense
Post reply on HN