SQL moving a field within a table

[wjh2303]

i have a table, it has fields, i would like to move one of the fields to the end of the table (its currently in the middleish). Is there an easy way to do this? if not what is the hard way?

thanks

 

[garrettroyce]

Location of a field within a table is pretty insignificant. I suppose you could add a new field to the end, create an UPDATE query and make new = old then delete the old field.

 

[xav0989]

As garret said, location is unsignificant. If you need your fields in a special order when you fetch data, well then, after the SELECT part, put the name of the fields in the order you want them. For instance:

SELECT field_1, field_6, field_2, field_8 ...

When inserting data, proceed in the same way:

INSERT ... (field_1, field_6, field_2, field_8) VALUES (value_1, value_6, value_2, value_8)

 

[wjh2303]

i know it doesnt make a differenc to the functionality, but one of the fields is of arbitrary length but all the others are pretty constant, so id like to put the arbitrary length field at the end so that everything lines up for easier editing when i export the table

 

[Livewire]

Export as a CSV/Comma Separated Value file, edit in excel, re-save as CSV

CSV just means separated like this:

value1,value2,value3,value4,value5
value1,value2,value3,value4,value5


Excel and OpenOffice Spreadsheet can open a CSV and save it back to CSV for importing, but you -can- still move the column to the end like garrett pointed out earlier.

 

[misson]

When you export or import a table, you can specify field order. No need to change the table definition. You should alter tables only if necessary for what you need to store in the table. An alternative is to create a view and export from that.

If you still really, really feel like you need to reorder columns, with MySQL you can use:

[FONT="Courier New"][URL="http://dev.mysql.com/doc/refman/5.1/en/alter-table.html"]ALTER TABLE[/URL] [I]table_name[/I] MODIFY [I]col_name[/I] [I]col_dfn[/I] AFTER [I]last_col_name[/I];[/FONT]

PostgreSQL doesn't support ALTER ... MODIFY (which should tell you something about what you're trying to do). Backup your data before doing this, because it could damage the data stored in the column. Make sure col_dfn is exactly the same as the old definition. You might need to re-create any indexes that include the moved column and perform other cleanup.

phpMyAdmin might let you move a column. If it doesn't, that should also tell you something about what you're trying to do.

 

[wjh2303]

ok, i guess aesthetics arnt worth risking it!