Column order

On Wednesday 23 April 2008 21:33, Vyacheslav Kalinin wrote:

Hello,

It is often convenient to have columns of a table in certain order (as
shown by psql or most GUI database explorers, it also affects INSERT's
without columns specified behavior) so as to most significant columns
to
come first, semantically close columns to be grouped etc, while the columns
might be added to the table in different order during development
process. So, the question is - is it an acceptable way to play with
pg_attribute's attnum and set it to needed value
or recreate the table is the only
way? On the related note - should the columns necessarily be numbered
from 1 up with the step of 1?

If you do this on a table with data in it you will almost certainly hoark your
data. On an empty table I'd just expect things to break. If you are really
interested in being able to re-order columns, search the archives for a patch
we saw ~ year or so ago that implemented storage level column ordering. The
discussion that followed laid out much of what would also be needed for
logical level column sorting. Work out those two bits and you'll have soon
have a patch for doing this the right way.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL