61–70 of 206 posts
Consider a ‘sales’ table which includes columns [time] and [sold_by_employee_id], and a periodized ‘employee’ table which includes columns [employee_id], [valid_from] and [valid_to] columns. There is a perfectly valid relationsship between the two tables, but you cant join them using only equal-statements (you need a between-statement as well)
Foreign keys exist for data integrity... something which is rather opaque to query onterface... So why do you believe that has place in a query?
In my opinion this proposal seems only to consider simple cases, but there are many not-so-simple relationsship types: Consider a ‘sales’ table which includes columns [time] and [sold_by_employee_id], and a periodized ‘employee’ table which includes columns [employee_id], [valid_from] and [valid_to] columns. There is a perfectly valid relationsship between the two tables, but you cant join them using only equal-state…
Is anyone actually likely to implement these based on some random gist on the internet, or are you just screaming into the void? Because, if you have that kind of clout, I've got an INSERT/SET syntax I'd like to put your way...
I'm a small PostgreSQL contributor since 2010 myself. I probably can't write the whole patch myself, but if there is enough interest, and if we can work out the details and address the problems raised in this thread and elsewhere, I'm pretty confident we can do it. If we ever get there, the next step would be a reference implementation, probably in PostgreSQL, or to discuss a proposal in the SQL committee.
To an outsider, proposing a change seems to require one to be part of a shady cabal of Big-5 employees, skilled in the art of hiding subtle, privacy-invading features into inscrutable, plain-text RFCs.
That or subjecting yourself to 30K+ what-abouters who deform your suggestion into something unrecognisable.
It's refreshing to see a straightforward, well-formatted proposal (even if I do slightly prefer the `FROM table1 x JOIN table2 y ON x.fk` syntax suggested in other comments).
Earlier quoted context omitted.
Similarly, I'd love some form of GROUP BY every column except for It feels silly when you are SELECTing a ton of columns, then you add a JOIN to a many-to-one relationship which you want to aggregate. Now you need to either make it a subquery (and hope the optimizer doesn't screw up) or duplicate all your SELECT expression (not even the identifiers) into the GROUP BY.
Queries like this won’t break but will silently fail upstream by missing data if column names change or are deleted. The explicit nature of SQL ensures that it will break positively which is a better type of failure in my opinion.
What I really need is something like: SELECT -col1, -col14 FROM table LIMIT 50; Where the minus sign means I don't want these two columns. I still don't see a way to do it easily (for Vertica and in Datagrip).
tangent question - what're some effective ways to learn how to write SQL?
There are also easy-to-google SQL Puzzles, if you're looking for something more advanced.
I would also recommend learning about query plans and how to read them, they're invaluable for query optimization.
I dig it! I prefer defining tables like this: CREATE TABLE category ( id int GENERATED ALWAYS AS IDENTITY, name text ); CREATE TABLE post ( id int GENERATED ALWAYS AS IDENTITY, category_id int REFERENCES category (id) ON DELETE CASCADE ); That is, category.id rather than category.category_id. But the USING clause doesn't work with that style, as far as I understand. This would make my queries nicer.
Will also add a "Drawbacks / Remaining issues" section to the Gist, from all the valuable comments so far in this thread, thank you all, positive as well as negative comments, all very helpful.
Earlier quoted context omitted.
Similarly, I'd love some form of GROUP BY every column except for It feels silly when you are SELECTing a ton of columns, then you add a JOIN to a many-to-one relationship which you want to aggregate. Now you need to either make it a subquery (and hope the optimizer doesn't screw up) or duplicate all your SELECT expression (not even the identifiers) into the GROUP BY.
You know, you can just write GROUP BY x, y, ... SELECT t.i+1, count(*) FROM table t GROUP BY 1 1 in this context means the first selected item (i.e. t.i+1). I know this works in PostgreSQL.