We generally prefer to reply in-line on these threads, as I’ve done below. Thanks.
On Wed, Aug 25, 2021 at 17:37 James Keener <jim@jimkeener.com> wrote:
I don't know that "generally better" is the correct description. There are a lot of things not covered by the SQL standard and it would be crazy not to make use of them, e.g. insert on conflict and distinct on, especially when they make the query more readable and more correct. Worrying about sql portability is almost always time not well spent and an invitation to do things not as well as they can be.
It’s generally better as it follows the SQL standard and is more flexible as it allows you to get top-N, as I said. DISTINCT ON has always been a hack and its use should be discouraged at this point.
When an UPSERT capability exists in the actual standard then it’s use should be promoted over other hacks that various vendors have implemented also.