Hacker News Reader

Comment by foreigner | Hacker News Reader

Allow trailing commas in SELECT and IN. Pretty please?

Omg yes please. I get that it might be slightly harder to parse but any language that doesn't support it inevitably annoys me. In pg and json you end up with runtime errors, programming languages lead to bigger diffs than necessary (adding an entry to a static list is a 2 line change instead of 1 line)

> programming languages lead to bigger diffs than necessary (adding an entry to a static list is a 2 line change instead of 1 line)

I think in ML-family languages it's fairly common to format like this, which avoids bigger diffs:

  foo = [ 1
        , 2
        ]

In C-style languages something similar goes as well, though perhaps used less commonly:

  int x[] = { 1
            , 2
  };

This is how I format sql code when I have control over it, have for years. People often recoil in horror, sometimes they ponder for a minute then switch over themselves.

It works very easily and consistently to center around the space and lead with the comma:

  select id
       , name
       , address
       , ts
    from table
   where condition
     and etc;

Doesn't this just move the problem to when you add a longer statement like "INNER JOIN"? Then the entire SQL statement becomes the diff?

Agreed. My standard is to pad with 12 spaces, eg:

  SELECT      foo
  FROM        bar

Which allows room for everything I commonly use, including INNER JOIN.

Sometimes yes. I don't think there are any perfect solutions and I won't pretend this one is. But usually I find those change less frequently than columns and where clauses, and if you write them out and declare them explicitly you rarely have to move the whole query over. It does happen though.

I tend to write

    SELECT a.foo
         , b.bar
         , array_agg(z.gumball) gumballs
      FROM zoo z
     INNER JOIN alpha a
           ON (z.z_id = a.z_id)
      LEFT JOIN baker b
           ON (a.a_id = b.a_id)
     WHERE z.last_modified > '2023-01-01'
       AND a.is_active
     GROUP BY 1, 2
     ORDER BY 2, 1
     LIMIT 100
    ;

Left side highlights the operations. Commas and "AND" delimit parts of each directive. The semicolon lines up on the left side to help visualize the end of each statement in a long chain of commands, especially DDL.

I also tend to capitalize the SQL keywords and leave the identifiers lower case.

My alternative is to follow with “1” so every preceding line can have a trailing comma or start a predicate clause with “1 = 1” so every subsequent line can start with “AND foo …”

This will still cause a two-line change if you insert at the beginning.

Granted, adding at the end is more common, but you can't fix the problem with tricks (short of putting every comma on a line by itself too).

Reply on news.ycombinator.com