How about trailing commas in SQL? • Hacker News Board

Re: How about trailing commas in SQL?

#131

Post by dangets » Tue, Feb 11, 2025, 4:01 PM UTC

atombender wrote: post #105

adornKey wrote: post #6

I feel the problem. When coding (not only in SQL) you often have to add something to the end of a list, and it is annoying that the end of the list is always special. You can't just copy some line and move it there. Also when moving things around you always have to take extra care at the end. So, my solution for this was always SELECT a , b , c FROM ... instead of: SELECT a, b, c, -- people want to allow trailing com…

I often do this with boolean WHERE filters when I'm doing interactive exploration on some data: SELECT ... WHERE foo = 1 AND bar = 2 I want to comment out a line (i.e. "--foo = 1"), but would break the syntax. The solution is to start with "WHERE true": SELECT ... WHERE true --AND foo = 1 AND bar = 2 Now you can comment/comment anything. (Putting the AND at end of each line has the same problem, of course, and requir…

I do the same, though my muscle memory is `1=1` instead of `true`.

Of course then you get editors/linters/coworkers that always point out that the 'true' is unnecessary. This also doesn't work with ORs (just swap to false), but in practice it seems it is always ANDs that is being used.

Re: How about trailing commas in SQL?

#132

Post by morkalork » Tue, Feb 11, 2025, 4:07 PM UTC

fifilura wrote: post #74

IIRC BigQuery started allowing trailing commas in queries around 2019. It all came as a very big surprise, out of thin air.

It's nice when bigquery and python together for data analysis that little things like that are congruent

Re: How about trailing commas in SQL?

#133

Post by nightpool » Tue, Feb 11, 2025, 4:10 PM UTC

Don't let the perfect be the enemy of the good. I'm sure that 99% of the people who are requesting trailing comma support care about select lists. And CREATE TABLE for the rest. Yes, the SQL standard has a huge surface area of custom syntax (rather then most programming languages which have a smaller number of composable atoms), which makes adding "consistent" syntax changes a challenge, but it doesn't mean you need to boil the ocean. Just get trailing comma select lists into a version I'm likely to use in the next decade and I will love you forever!

Re: How about trailing commas in SQL?

#134

Post by nightpool » Tue, Feb 11, 2025, 4:14 PM UTC

netcraft wrote: post #120

sunnybeetroot wrote: post #113

Earlier quoted context omitted.

Wouldn’t just trailing solve your issue? SELECT a, b, c, From Foo

in 20 years of writing SQL, preceding commas is so much better IMO. Its so easy to miss a comma at the end of a long expression. preceding commas means you can never forget them. Then if I can have an extra leading comma, I can reorder, comment out or remove, or add a column at any point in the list without having to think about the rest of the projection. Also diffs are cleaner, it only highlights the rows that have…

It's only easy to miss a comma at the end of a long expression because you need to calculate whether it should be there in the first place. If commas were always required unconditionally, it wouldn't be a problem.

Re: How about trailing commas in SQL?

#135

Post by Willamin » Tue, Feb 11, 2025, 4:14 PM UTC

yellowapple wrote: post #123

Why even have commas at all? This is a non-issue for the likes of Tcl and Lisp and your average shell-scripting language and what have you; whitespace is already a good enough delimiter.

Genuinely, I'd love to see this approach be taken: allow for a set of characters to be used as list delimiters. I personally like the set to be comma, semicolon, and newline, but of course this set would need to be varied depending on other syntax (e.g. in SQL, we wouldn't want semicolon to be used for this).

Having newline be a valid list separator is particularly nice because it solves the "trailing comma" and "comma-first" style workarounds in a visually elegant way. The newline already provides a visual separator; we can already tell that we're at the end of most lists by way of having another keyword appear next without needing to rely on a lack of commas, for example:

    select
        id
        name
        email
    from users

Re: How about trailing commas in SQL?

#136

Post by nerdponx » Tue, Feb 11, 2025, 4:15 PM UTC

nightpool wrote: post #133

Don't let the perfect be the enemy of the good. I'm sure that 99% of the people who are requesting trailing comma support care about select lists. And CREATE TABLE for the rest. Yes, the SQL standard has a huge surface area of custom syntax (rather then most programming languages which have a smaller number of composable atoms), which makes adding "consistent" syntax changes a challenge, but it doesn't mean you need…

Most of that additional syntax surface area would also benefit from trailing commas. For example, `SELECT * EXCLUDE (a, b, ...)`, or even `FROM a, b, ...`.

Re: How about trailing commas in SQL?

#137

Post by nightpool » Tue, Feb 11, 2025, 4:16 PM UTC

dv_dt wrote: post #106

nickcw wrote: post #7

I feel like this ship has sailed. SQL has been around for more than 50 years and everyone who needs to generate it has already put that extra `if` statement in to suppress trailing commas. What annoys me far more often is the lack of support for trailing commas in JSON.

I feel the same way about SQL too, it's set and difficult to shift. But I also look at PRQL longingly - https://prql-lang.org/

Have you seen the Google BQ pipe syntax? https://cloud.google.com/bigquery/docs/reference/standard-sq...

Feels like it does 75% of what PRQL does while still staying somewhat backwards compatible. Already works in BQ if you opt in.

Re: How about trailing commas in SQL?

#138

Post by eftpotrm » Tue, Feb 11, 2025, 4:16 PM UTC

yellowapple wrote: post #123

Why even have commas at all? This is a non-issue for the likes of Tcl and Lisp and your average shell-scripting language and what have you; whitespace is already a good enough delimiter.

Because there's existing implementations that would interpret that as aliasing column names.

``` SELECT Field AS Renamed, OtherField AS AlsoRenamed ```

and

``` SELECT Field Renamed, OtherField AlsoRenamed ```

are semantically equivalent.

Re: How about trailing commas in SQL?

#139

Post by atombender » Tue, Feb 11, 2025, 4:16 PM UTC

NoMoreNicksLeft wrote: post #114

atombender wrote: post #105

Earlier quoted context omitted.

I often do this with boolean WHERE filters when I'm doing interactive exploration on some data: SELECT ... WHERE foo = 1 AND bar = 2 I want to comment out a line (i.e. "--foo = 1"), but would break the syntax. The solution is to start with "WHERE true": SELECT ... WHERE true --AND foo = 1 AND bar = 2 Now you can comment/comment anything. (Putting the AND at end of each line has the same problem, of course, and requir…

God, everyone's going to hate me for this. (I will have earned it, I think.) SELECT ... WHERE foo = 1 AND bar = 2 Each keyword gets a new line, the middle gutter between keyword and expressions stays in the same place, and things get really, really fugly if I need a subselect or whatever. Any given line can be commented out. (And no, none of that leading comma bullshit, somehow that looks nasty to me.) Downvote this…

You may have missed what I was getting at.

In order to quickly comment out the "foo = 1" here, you cannot simply comment out the whole line because it would become syntactically invalid:

     SELECT ...
     --WHERE foo = 1
       AND bar = 2

I have a single keyboard shortcut to comment/uncomment a line because I like to work briskly with as little unnecessary typing.

It has nothing to do with indentation or being "fugly". I'm talking about interactive exploration of prototyping when the final SQL isn't set in stone.

Re: How about trailing commas in SQL?

#140

Post by skeeter2020 » Tue, Feb 11, 2025, 4:17 PM UTC

adornKey wrote: post #6

I feel the problem. When coding (not only in SQL) you often have to add something to the end of a list, and it is annoying that the end of the list is always special. You can't just copy some line and move it there. Also when moving things around you always have to take extra care at the end. So, my solution for this was always SELECT a , b , c FROM ... instead of: SELECT a, b, c, -- people want to allow trailing com…

I too prefer leading commas, especially useful when you're prototyping a query. I also picked up the ORM trick of starting your WHERE clause with 1=1 so that every meaningful filter can start with AND ... I'm not as consistent with this one, but it's handy too.

I catch (friendly) flak for my zealot SQL formatting (capitalization, indenting) and know it doesn't impact the execution, but there's something about working in logic / set theory that matches with strict presentation; maybe helps you think with a more rigid mindset?