code request "is [not] distinct from"

[tballard]

The "is not distinct from" feature (basically "=" that handles nulls, too) would be as easy to implement as "eq" or "gt" or something and much appreciated. I don't think I missed it ...

[seratch]

Hi @tballard, thanks for sharing this but I still don't get the point. Would you mind sharing a bit more detail with code examples or related documents on the syntax?

[tballard]

Anywhere you can write the condition "t1.col1 = t2.col2" you can write "t1.col1 is not distinct from t2.col2", which is the same except for being true if both are null. You can also take out the "not" as you might expect. The syntax is awkward, no doubt because they were trying not to add any new keywords. I just thought it would be easy to add to your DSL, and hey, you could probably improve on the name. https://www.postgresql.org/docs/current/functions-comparison.html

If you are looking for useful features to add, two more I use often that I didn't see any mention of are the "distinct on" feature that allows dividing into ranges and pulling a particular value from the range and the "with" construct that allows defining queries and then using them like tables in the succeeding queries. And "exists" as in "select exists (select something from wherever where whatever)" ...

[seratch]

Hi @tballard, thanks for your detailed reply! Since "is not distinct from" is not a commonly implemented syntax in other RDBMSs (perhaps, PostgreSQL only?), so I hesitate to add it to this library's Query SDL at this moment.

You can go with .append(sqls"${a.col1} is not distinct from ${b.col2}") instead.

Although I am not planning to add DSLs for it in the short term, let me keep this issue open for future discussions.

[tballard]

Thanks for directing my attention to ".append". It should be helpful with some postgresql quirks.