News for hackers. Stuff that matters.
Re:User-defined Order in SQL (2018) by computerthings on Tuesday August 13, 2024 @12:01AM (#41232090)

    update todos set pos = pos+1 where pos >= 3 ORDER BY pos DESC
Am I missing something?
Re:User-defined Order in SQL (2018) by seanhunter on Tuesday August 13, 2024 @03:00AM (#41233009)
You are not[1]. This is not a complex problem but the author has managed to make a blog article out of it and come to the conclusion that rational numbers are required.

The reason the author gives for rejecting your proposal is they have added a uniqueness constraint which makes the update awkward but you can indeed work around that in a variety of ways or just drop the uniqueness constraint altogether.

[1] Although update doesn't have an "order by" clause[2] but I'm assuming you mean 'update' and then subsequently 'select' with the order by. I think if you actually want an update to proceed sequentially in some order you have to use a cursor.

[2] Author is using postgres so this would be the relevant doc https://www.postgresql.org/docs/current/sql-update.html

Re:User-defined Order in SQL (2018) by facturi on Tuesday August 13, 2024 @03:11AM (#41233062)
It works in MariaDB.
Re:User-defined Order in SQL (2018) by sam_lowry_ on Tuesday August 13, 2024 @07:14AM (#41234297)
You are effectively updating many todos. The benefit of the solution presented by the author is not about performance, but rather minimizing change.

Imagine a situation when multiple clients keep copies of todos and rely on optimistic locking for updates.

This is a cool little problem brilliantly solved.

Re:User-defined Order in SQL (2018) by beart on Tuesday August 13, 2024 @11:46AM (#41236568)
In that case, your update filters on the user_id.
Re:User-defined Order in SQL (2018) by sam_lowry_ on Friday August 16, 2024 @06:03AM (#41264903)
In my real-life case, these were not todos, but cases reviewed by public agents.

These rotate between different agents and supervisors. The ownership is just an attribute intended for humans.

OTOH, rebalancing the order at night is not a problem.