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. id
2. *account_num*
3. dob
4. start_date
5. *type_id*
6. preferred_status
7. */job_id/*
8. comment

would end up like:

1. id
2. dob
3. *job_id*
4. *type_id*
5. *account_num*
6. start_date
7. preferred_status
8. comment

Am assuming positions are numbered from 1 onwards.

Cheers,
Gavin