Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

order of table columns: alter table add/modify ... position before/after ...

Carsten KaftanFeb 13 2015 — edited Jan 11 2016

#

# A supplementary annotation (at a current score of -40):

# As there were some comments regarding the usefulnes of such a feature,

# I added an example at the bottom of this text.

#

I would like to resubmit the not very popular "Need a command to reorder columns in a table" of Torsten Kleiber with a different aspect: The position of table columns ultimately is not relevant, but a quick survey is very much simplified by a clearly arranged table layout (usage of "select * from …", etc.).

At the moment it is very laborious to keep evolving tablestructures well laid out. I would appreciate very much an extension of the "alter table"-syntax like the following:

ALTER TABLE [ schema. ] table

  { MODIFY | ADD }

  ( column_name [...]

    [position { FIRST | LAST | { BEFORE | AFTER } column_name }]

  )

P.S.

MySQL allows ALTER TABLE ADD COLUMN AFTER COLUMN.

#

# Consider a table with address-entries and further informations

#

# NAME            DOMICILE         LAST_CONTACT  COMMENT

# "Willi Wiberg"  "82152 Habsburg" 12-Dec-14     "VIP"

#

# Later, you want to differentiate between given and family name and

# separate the ZIP-Code from the place name.

# The altered table would look like this (in "natural column order"):

#

# NAME            DOMICILE         LAST_CONTACT  COMMENT  FIRST_NAME ZIP_CODE

# "Wiberg"        "Habsburg"       12-Dec-14     "VIP"    "Willi"    82152

#

# With positioned and renamed columns the layout would be much clearer:

#

# FAMILY_NAME FIRST_NAME ZIP_CODE CITY       LAST_CONTACT  COMMENT

# "Wiberg"    "Willi"    82152    "Habsburg" 12-Dec-14     "VIP"  

#

# Reordering of columns can be a useful feature for every table which

# contains "groups" of columns belonging together with regard to content.

#

# (I really do have difficulties to understand why it is so difficult

# to see benefits and possible applications of reordering columns - ?)

#

Comments
Post Details
Added on Feb 13 2015
23 comments
37,114 views