Re: logical column ordering

On 28/02/15 09:09, Josh Berkus wrote:
> Tomas,
>
> So for an API, 100% of the use cases I have for this feature would be
> satisfied by:
>
> ALTER TABLE ______ ALTER COLUMN _____ SET ORDER #
>
> and:
>
> ALTER TABLE _____ ADD COLUMN colname coltype ORDER #
>
> If that's infeasible, a function would be less optimal, but would work:
>
> SELECT pg_column_order(schemaname, tablename, colname, attnum)
>
> If you set the order # to one where a column already exists, other
> column attnums would get "bumped down", closing up any gaps in the
> process.  Obviously, this would require some kind of exclusive lock, but
> then ALTER TABLE usually does, doesn't it?
>
Might be an idea to allow lists of columns and their starting position.

ALTER TABLE customer ALTER COLUMN (job_id, type_id, account_num) SET 
ORDER 3;

So in a table with fields:
1. id2. *account_num*3. dob4. start_date5. *type_id*6. preferred_status7. */job_id/*8. comment


would end up like:
1. id2. dob3. *job_id*4. *type_id*5. *account_num*6. start_date7. preferred_status8. comment

Am assuming positions are numbered from 1 onwards.


Cheers,
Gavin