4 ms·

Can you re-order columns yet? http://wiki.postgresql.org/wiki/Alter_column_position http://wiki.postgresql.org/wiki/Alter_column_position 18 years, and still,

• ww520 14y ago
  Can you just specify the order in your query? Why do you care how the physical order is stored? Or do you even care about the physical order? Would an "apparent" logical order be sufficient?
  • alexanderh 14y ago
    If you're using postgresql strictly as a programming data store, its fine. Your app will certainly abstract away the ordering of the columns Its just nice though, if you often find yourself working on raw tables using one of the many Admin tools. Sometimes you just need it to work like Excel to be productive. MySQL makes all of that trivial. PostgreSQL obviously keeps track of the order you created the columns, so being able to edit this ordering seems trivial in my mind. It just seems odd that there certainly is some demand there for it, yet nobodys picked up the slack in 18 years. Obviously there are workarounds. Why dont they include the work arounds as scripts with the distrobution, etc? Maybe I will try to implement something and submit a patch. No experience in database dev though :( Like I said though, its not a deal breaker. I'm just used to Open Source projects over-implementing features. Not under-implementing them. Especially after 18 years.
    • e12e 14y ago
      > Its just nice though, if you often find yourself working on raw tables using one of the many Admin tools. Sometimes you just need it to work like Excel to be productive. Clearly this is a bug in the admin tool? I seem to recall MS SQL allows you to "drag columns around" -- obviously doing absolutely nothing to the database -- it's just a view. After all rows are just relational tuples -- they have no ordering.
      • alexanderh 14y ago
        True, a lot of the tooling for PostgreSQL is in its infancy when compared to something like MySQL. This problem definitely could be abstracted away by the tool. But because PostgreSQL doesnt support it natively, a side effect has been that many of the popular tools, comparable to ones in MySQL, dont have this feature either. Anyone have any good suggestions for web frontend to PostgreSQL that supports reordering of columns as easily as MySQL? phpPgAdmin doesnt seem to compare to phpMyAdmin. Admittedly I probably should be using a more robust OS native PostgreSQL admin tool, and not a web fronted. But alas, all of this is solved with MySQL quite nicely. I would like to see PostgreSQL catch up.
    • ww520 14y ago
      So you only care about the logical ordering of the columns. It seems trivial then to add a column mapping layer in the metadata to map the user-defined apparent order to the physical order so that select * would return the order you want. In relational algebra the ordering of the rows and the ordering of the columns are undefined. It's up to the database to arrange it in the best way possible. The ordering of rows and columns is specified when queried.
• jacques_chester 14y ago
  Oracle and SQL Server can't do this either. Most databases are, by default, row stores. Moving columns around on disk kinda sucks in such situations, and there's no need to when you can specify results in any order by naming fields. You name fields in your queries, right? Right?
  • alexanderh 14y ago
    I know, I know. Its a minor gripe. But think about how trivial something like this would be to implement. And with the power of an Open Source project as big as Postegre, just seems odd nobody's had a weekend to knock it out. Here we are discussing all these cool advanced features, and it doesnt even have something as simple as this :P I'll certainly try, if I thought they'd ever accept a patch from a total newb. PostgreSQL obviously keeps track of the column order, as far as the order you created them in goes. So being able to edit this order doesnt seem like that tall of an order. Would be a pretty convenient feature that many other DB's do infact have. Its just a nicety that PostgreSQL should have, if its hoping to win over people who are familiar with MySQL, which does sorta seem like its goal these days.
    • deleted 14y ago
      [deleted]
    • jacques_chester 14y ago
      I'm going to guess that MySQL can do this largely as a side-effect of swappable engines. There is, however, a lot to be said for close coupling between query planning, on-disk format, indexing and so forth. edit: as for patching, the PostgreSQL source is some of the best I've ever read. Carefully organised, fastidiously documented, immaculately consistent to coding standards. It's a delight.
    • pilif 14y ago
      Trivial? The rows are ordered in creation order because that's how they are stored on disk. So to be able to move columns around, you either have to rewrite the whole table (needing a long-lasting exclusive lock which most of the schematic altering operations in postgres do not require) or you introduce another abstraction to keep track of the order which you then have to use for every query to get the correct position within the row you just read. This always means more complexity and a performance overhead and frankly provides next to zero value because columns are named in queries anyways (or the order becomes meaningless when joining)
• jaytaylor 14y ago
  Sure it'd be nice.. but really, what's the difference? The sooner you stop worrying about that the sooner you can start building awesome shit with it!
• bsg75 14y ago
  Where is this an issue, where you dont just specify column order in a query? If from something like Excel, why not use a passthrough query. Does any major RDBMS support this?
• JoachimSchipper 14y ago
  Not the most elegant solution, but a view plus some triggers will let you create a fully functional "table" with any column order you please.