There is one non-SQL related reason that I like to be able to order
columns, at least the way they are displayed whenever the table is
described: human comprehension. For example, I like to group all keys
in a table before data, that includes primary as well as foreign keys.
So, say I'm building on to an existing application and I need to do an
ALTER TABLE on an existing table to add a foreign key to an existing
table. I'd like that key to be listed with the other keys, but
presently that's not possible in a simple way and, to be honest, I
usually just go without as the process you've described below is too
prone to user (human) error when dealing with live, sensitive data for
me to want to mess with it.
Markus Schaber wrote:
> Hi, Penchalaiah,
>
> Penchalaiah P. wrote:
>
>
>> now I want to add one more field in this table.. but that field has to
>> come next to cda_no.. I mean as a 3^rd field.. If I am adding that field
>> it is coming last field …
>>
>
> In SQL, field order in the table is not given by design.
>
> A "SELECT * FROM table" might even give you the columns alphabetically
> ordered, or in a different random order each time in a different server
> implementation.
>
> If you need the colums in a specific order, use "SELECT foo, bar, baz
> FROM table" or create a View.
>
> All relevant SQL constructs (SELECT, INSERT, UPDATE, COPY, etc.) let you
> specify the columns explicitly to guarantee a given order.
>
>
>> may I know how it is possible to that table…
>>
>
> If you _really_ want to do that despite what I wrote above, you have
> several possibilities:
>
> - COPY the table to some file, drop the table, recreate the table with
> the desired new column order, and then COPY the table back using an
> explicitly specified, correct row order.
>
> - use CREATE TABLE ... AS SELECT to select the data into a new table,
> drop the old table, rename the new one to the old one.
>
> In both cases, you've to recreate all missing indices, foreing key
> constraints etc.
>
>
> HTH,
> Markus
>
>
>
--
erik jones <erik@myemma.com>
software development
emma(r)