51–60 of 134 posts
I love the attitude towards ergonomics over standard compliance. And you'll see why SQL has never been really portable across databases ;-)
Lots of great additions. I will just highlight two: Column selection : When you have tons of columns these become useful. Clickhouse takes it to the next level and supports APPLY and COLUMN in addition to EXCEPT, REPLACE which DuckDB supports: - APPLY: apply a function to a set of columns - COLUMN: select columns by matching a regular expression (!) Details here: https://clickhouse.com/docs/en/sql-reference/statement…
Allow referencing columns defined previously in the same query would make duckdb competitive for data analytics. Without that one has to chain With statements for just the tiniest operations. select 1 as x, x + 2 as y, y/x as z;
Earlier quoted context omitted.
because columns can be added to tables in production databases, so any time you use select * you run the chance the number of columns changing and breaking anything you wrote.
That's a problem with select * in general, not a problem with using EXCLUDE with select *. So that still doesn't explain why it's not in SQL to begin with.
Worst, by far, than a column you didn't expect being added is a column you did expect being removed. Depending on how thorough your integration tests are (and ideally they should be pretty thorough) you could suddenly start getting strange array key access (or object key unfound) errors somewhere on the other side of the codebase.
I love the attitude towards ergonomics over standard compliance. And you'll see why SQL has never been really portable across databases ;-)
Earlier quoted context omitted.
Excellent question! I'll jump in - I am a part of the DuckDB team though, so if other users have thoughts it would be great to get other perspectives as well. First things first - we really like quite a lot about the SQLite approach. DuckDB is similarly easy to install and is built without dependencies, just like SQLite. It also runs in the same process as your application just like SQLite does. SQLite is excellent a…
One of SQLite's most appealing aspects to me is using it as an application file format, as described in this article: https://www.sqlite.org/appfileformat.html How does DuckDB compare in that aspect? Does it have the same kind of guarantees of robustness, incorruptibility and performance (especially reading/writing binary blobs) that SQLite does? In any case: DuckDB looks great, nice work! Good to have more players i…
Earlier quoted context omitted.
because columns can be added to tables in production databases, so any time you use select * you run the chance the number of columns changing and breaking anything you wrote.
That's a problem with select * in general, not a problem with using EXCLUDE with select *. So that still doesn't explain why it's not in SQL to begin with.
It appears how this works is that is selects all columns and then EXCLUDES only the column's specified, the reason this doesn't exist in normal SQL is because it is a terrible idea. This is something that will break at many companies with large technical debt if it is ever used.
Lots of great additions. I will just highlight two: Column selection : When you have tons of columns these become useful. Clickhouse takes it to the next level and supports APPLY and COLUMN in addition to EXCEPT, REPLACE which DuckDB supports: - APPLY: apply a function to a set of columns - COLUMN: select columns by matching a regular expression (!) Details here: https://clickhouse.com/docs/en/sql-reference/statement…
JSON is the other one where it annoys me, but luckily I rarely hand-write any JSON anymore (and there are semi-solutions for this like json5).
In code I always add trailing commas to anything comma-separated. It makes editing simpler (you can shuffle lines without thinking about commas). In a diff or blame it doesn't show adding a comma as a change.
SQL is the one spot where this doesn't work, and it's a constant foot-gun as I often don't remember until I run and get a syntax error.
Earlier quoted context omitted.
That's a problem with select * in general, not a problem with using EXCLUDE with select *. So that still doesn't explain why it's not in SQL to begin with.
I've always viewed SELECT * as a convinence for schema discovery and a huge bonus for subqueries - our shop excludes its use at the top level in production due to the danger of table definitions changing underneath... but we happily allow subqueries to use SELECT * so long as that column list is clearly defined before we leave the database. Worst, by far, than a column you didn't expect being added is a column you di…