4 ms·
Oh thanks for sharing this! I love all of those, and most seem like they'd be easy sugar over the existing syntax. The biggest missing feature from those that I
by aftbit 4y ago
Oh thanks for sharing this! I love all of those, and most seem like they'd be easy sugar over the existing syntax. The biggest missing feature from those that I would really enjoy in data exploration tasks (though not in PROD) would be automatic JOIN ON selection based on foreign keys.
Example:
SELECT users.id, COUNT(*)
FROM users
JOIN orders ON AUTO
WHERE orders.created_at > NOW() - '7 day'::interval
GROUP BY ALL
This would only work if there was an obvious path to do the join. In this case, I'm imagining that the `orders` table might have a `user_id` column which is a foreign key into the `users` table.
- sroussey 4y agoMySQL has done this for ages by using `NATURAL JOIN`. Example pulled from Google: https://sebhastian.com/natural-join-mysql/ https://sebhastian.com/natural-join-mysql/
- ttfkam 4y agoSo does Postgres. Unbeknownst to the OP, supported it since at least v7.2 (aka a looooooong time ago). https://www.postgresql.org/docs/7.2/queries-table-expressions.html https://www.postgresql.org/docs/7.2/queries-table-expression...
- discretion22 4y agoThat sounds very close to NATURAL JOIN which is already present[0] although that does rely on the typical convention of FK columns being named the same on parent and child (related) tables. I think you are suggesting some sort of lookup based on the defined FK relation, but that would be confused by situations where tables have multiple FK relations, such as tables with values restricted by a lookup value table (or more than 1 such FK). Those are pretty common, so I could see the 'AUTO' feature breaking down quickly. I think that is why the NATURAL JOIN approach is taken and that basically does what I believe you are describing, provided the column naming is matched. [0] https://www.postgresql.org/docs/15/queries-table-expressions.html#QUERIES-FROM https://www.postgresql.org/docs/15/queries-table-expressions... [edit:spelling]
- Rizz 4y agoYou could use the name of the foreign key, together with FKs namespaced to the table they are on would allow very expressive joining, and the query might even survive schema changes. ORMs tend to work like this.