On Wednesday, July 29, 2020, Holger Jakobs <holger@jakobs.com> wrote:
That columns appear to have some kind of order and show in tools with a stable order has technical reasons and is not rooted in relational theory.
Technical reasons are real reasons. The order of columns is very real, guaranteed (beware of inherits), thing in PostgreSQL whereas there is no guarantee regarding row order outside of a select query order by. That said I do agree that fulfilling “ocd” drives isn’t a strong use case to work on the feature.
As for the theory, a tuple is an ordered list so the ordering of columns is a precise application of that definition and a relation is just a set (i.e., unordered) of tuples. So the claim this isn’t rooted in relational theory just seems wrong.
The issue here is to ensure that a given column always shows up at the end, even after you added other columns. In other words, it is a kind of "ALTER TABLE ... INSERT COLUMN AT ..." instead of "... ADD COLUMN ...". It is more about data modeling than relational theory...