Hacker News Reader

Comment by vbezhenar | Hacker News Reader

Here're little things that would make my life better when I'm generating SQL queries:

1. Support trailing commas.

2. Add operator `is` which works like `==` but for nulls it returns `true`. Could also remove automatic type casting, I'd be happy about that, never liked it.

3. Support trailing `or`-s and `and`-s.

So query could look like

    select a, b,
    from t
    where (a.x is :x and a.y is :y and) or
          (b.x is :x and) or

View on HN

Trailing "and"s and "or"s looks so wrong to me; it reads so strongly like a syntax error. How about Boolean functions instead, like so:

    select
      a,
      b,
    from t
    where any(
      all(a.x is :x, a.y is :y,),
      b.x is :x,
    )

This way you get the uniformity you want from trailing commas, without the syntax looking quite so weird.

That's even better.

As an aside, avoid OR as much as possible in your WHERE clauses. Adjust your relations if necessary. ORs can really blow the planner up and slow things down. ANDs are reductive wrt indexes. ORs make multiple passes on the indexes necessary.

It's not always possible to avoid, and it makes perfect sense sometimes, but it'd be better if folks didn't lean into OR so much.

What is the reason for trailing commas and ands/ors?

So that you can generate SQL more easily, and (for hand-written statements) so that you can re-order lines more easily. Many programming languages support trailing commas.

Reply on news.ycombinator.com