https://duckdb.org/2022/05/04/friendlier-sql.html

I really dig some of the SQL syntax DuckDB has adopted, and I'd love to start a conversation on if some of these can be adapted to SQLite.

I use SQLite for a lot of analysis work, and I come across many of these problems daily. Here are some specific ones:

SELECT * EXCLUDE

SELECT * EXCLUDE (jar_jar_binks, midichlorians) FROM star_wars

SELECT * REPLACE

SELECT 
    * REPLACE (movie_count+3 as movie_count, show_count*1000 as show_count)
FROM star_wars_owned_by_disney

GROUP BY ALL

SELECT
    systems,
    planets,
    cities,
    cantinas,
    SUM(scum + villainy) as total_scum_and_villainy
FROM star_wars_locations
GROUP BY ALL

String Slicing

SELECT 'I love you! I know'[:-3] as nearly_soloed;

Trailing Commas

SELECT
    x_wing,
    proton_torpedoes,
    --targeting_computer
FROM luke_whats_wrong
GROUP BY
    x_wing,
    proton_torpedoes,

Personally, I think trailing commas, string slicing, and SELECT * EXCLUDE would have the biggest improvements in my work. Would love to hear what you all think!

EXCLUDE

In SQLite EXCLUDE is already a reserved word for other uses - but I like the idea of being able to select * (minus a few fields)

GROUP BY ALL

Am I correct to think that it means to Group by all bare fields in the select? Not a bad idea. And stating it explicitly would differentiate it from SQLite's current great feature of picking bare fields from a single row that satisfies the MAX() or MIN() aggregates, if specified.

I honestly don't care for the string slicing or introducing new function styles, but allowing Trailing commas I can get behind. I'm all for strict code, but a dangling comma at the end of a table definition which errors out serves only to annoy, it has never been a helpful error or protected me from some horrible schema/query mistake. Same for values clauses - would love to see:

INSERT INTO t(a, b) VALUES
(1, 10),
(2, 20),
(3, 40),
;

be a valid query.

I would love trailing commas! I do lots of SQL code generation. While the idiom to handle commas is well-known, having to do it is a constant irritant.

+$.02

String Slicing

SELECT 'I love you! I know'[:-3] as nearly_soloed;

As effectively an alias for the substr() function?

I thought the same thing. I can understand adding new functions that are not found in a language and it makes sense to add it, but just to make things look and feel like others, no. Just thinking out-loud. :-)

Maybe not; it depends on whether the value after the : is a length or an index, and nobody's actually said what it means. Still feels to me like something that can be a function.