SQL Zoo: The Postgres engine on sql zoo is bugged.

[DrantDumani]

Checks

• [X] This is not a duplicate of an existing issue (please have a look through our open issues list to make sure)
• [X] I have thoroughly read and understand The Odin Project Contributing Guide
• [X] Would you like to work on this issue?

Describe your suggestion

I posted about this in the discord, but I figured this was the proper way to go about this. The POSTGRES option on SqlZoo is bugged. I was doing everything in MySql and I switched to Postgres because I saw the chat. However, it results in some odd behavior.

Some examples:

In the More Join tutorial, there's a question that asks you to find the id of the film titled 'Casablanca'. This is the result in postgres

SELECT id FROM movie WHERE title = 'Casablanca';
-- Outputs TWO ids!
-- id
--  132689
--  60576357

This isn't the case in MySql where you only get one id. The next question, which asks you to list the cast list for Casablanca is also affected.

SELECT name FROM actor JOIN casting ON actor.id = actorid
WHERE casting.movieid = 132689;
-- Outputs no confirmation on a correct answer
-- The second id just outputs an empty list

And then there's the NULL tutorial, which is what prompted me to do some research and make this post to begin with. The question asks you to list all teacher in the teacher table who don't have a department. Here's a link to the tutorial: https://sqlzoo.net/wiki/Using_Null But there's one problem.

SELECT name from teacher WHERE dept IS NULL;
--ERROR: relation "teacher" does not exist LINE 1: SELECT name from teacher WHERE dept IS NULL; ^

According to this post on postgresql.org, this has been an issue with Sql zoo since 2008. https://www.postgresql.org/message-id/396486430808120859p12f01d57uc050e458f7a1309a@mail.gmail.com Sorry about bringing all of this up after you guys made the PR to change from mySql to Postgres for this projecy. I was in the middle of doing everything in MySql before it was brought up.

As a solution, I think we can go back to mentioning the use of MySql. But there can also be a note saying that, while future projects would be using Postgres instead, the solutions to problems on SqlZoo do not require anything further than engine agnostic sql syntax, similar to the assignments in the previous lesson that used sqlite.

Path

Ruby / Rails, Node / JS

Lesson Url

https://www.theodinproject.com/lessons/node-path-databases-sql-zoo

(Optional) Discord Name

Elemeandor

(Optional) Additional Comments

No response

[MaoShizhong]

28490 reverting the Postgres commit. We can discuss how we adjust the verbiage of the MySQL if necessary after we confirm what can/can't be done.

[wise-king-sullyman]

Did reverting that commit resolve this issue, or were you wanting to keep this issue open to be a place of discussion moving forward @MaoShizhong ?

[MaoShizhong]

@wise-king-sullyman with the revert, the current wording seems odd given that both pathways use postgres later (though I believe the Rails course doesn't spend as much time with raw postgres as the Node course does).

I'm not fully familiar with the assigned SQLZoo content yet, so I'm not 100% sure if any of the assigned content might include things where MySQL and Postgres will differ.

If it's confirmed we cannot use the Postgres engine due to SQLZoo's own bugs, then we should go with MySQL but amend the assignment wording to clarify why we use that. More so if there are even small things that differ between them that a learner may encounter in even just one of the exercises.

But since I'm not fully familiar myself, I'd prefer if I can get confirmation from a couple others who are more familiar.

[github-actions[bot]]

This issue is stale because it has had no activity for the last 30 days.

[wise-king-sullyman]

@MaoShizhong do you know if anything else has happened with this discussion?

[MaoShizhong]

I don't think there was much. I haven't been keeping an eye on the SQL and Node Postgres/Prisma stuff since the release, so I'm not particularly up to date on whether there have been any long term issues with things being kept as is with the MySQL engine.

@DrantDumani Have you any thoughts about this since you opened the issue? @01zulfi Any thoughts yourself?

If it seems that all is well for now, we could probably just leave it all as is. People do that first before they encounter Postgres later on, so probably not that big of an issue to dive into?