Derby
  1. Derby
  2. DERBY-1515

Provide ALTER TABLE functionality to change a column's data type

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Won't Fix
    • Affects Version/s: 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6
    • Fix Version/s: None
    • Component/s: Documentation, SQL
    • Labels:
      None

      Description

      Derby should provide a feature which allows a user to change the data type of an existing column in an existing table.

      Currently, there exists the statement:

      ALTER TABLE tablename ALTER COLUMN columnname SET DATA TYPE datatype

      However, this statement currently only allows increasing the length of a VARCHAR column. You are not allowed to decrease the width or to change the data type.

      It would be nice if this restriction could be lifted, and the datatype could be changed.

        Issue Links

          Activity

          Hide
          Bryan Pendleton added a comment -

          Once Derby supports ADD COLUMN, DROP COLUMN, and RENAME COLUMN,
          it seems to me that you can combine these tools to modify a column's data
          type as follows. Suppose that I want to change column "c1" of table "t" to
          new datatype NEWTYPE:

          1) ALTER TABLE t ADD COLUMN c1_newtype NEWTYPE;
          2) UPDATE t SET c1_newtype = c1;
          3) ALTER TABLE t RENAME COLUMN c1 TO c1_oldtype;
          4) ALTER TABLE t RENAME COLUMN c1_newtype to c1;
          5) ALTER TABLE t DROP COLUMN c1_oldtype;

          While this isn't as compact or probably as efficient as the single-statement
          technique proposed in the description above, it means that you can accomplish
          the task with existing functionality, and therefore maybe it would be "good enough".

          Note that this also has the advantage that the user can specify whatever special
          processing is necessary to convert the existing data values from the old type to
          the new type, as part of the UPDATE statement in step 2. With the single-statement
          form proposed in the description, it's not clear how Derby would have enough
          information to know how to convert the values in cases where a simple cast was
          inadequate.

          Show
          Bryan Pendleton added a comment - Once Derby supports ADD COLUMN, DROP COLUMN, and RENAME COLUMN, it seems to me that you can combine these tools to modify a column's data type as follows. Suppose that I want to change column "c1" of table "t" to new datatype NEWTYPE: 1) ALTER TABLE t ADD COLUMN c1_newtype NEWTYPE; 2) UPDATE t SET c1_newtype = c1; 3) ALTER TABLE t RENAME COLUMN c1 TO c1_oldtype; 4) ALTER TABLE t RENAME COLUMN c1_newtype to c1; 5) ALTER TABLE t DROP COLUMN c1_oldtype; While this isn't as compact or probably as efficient as the single-statement technique proposed in the description above, it means that you can accomplish the task with existing functionality, and therefore maybe it would be "good enough". Note that this also has the advantage that the user can specify whatever special processing is necessary to convert the existing data values from the old type to the new type, as part of the UPDATE statement in step 2. With the single-statement form proposed in the description, it's not clear how Derby would have enough information to know how to convert the values in cases where a simple cast was inadequate.
          Hide
          Bryan Pendleton added a comment -

          Knut Anders pointed out that it isn't necessary to rename column c1 to c1_oldtype.
          The technique can be simplified to:

          1) ALTER TABLE t ADD COLUMN c1_newtype NEWTYPE;
          2) UPDATE t SET c1_newtype = c1;
          3) ALTER TABLE t DROP COLUMN c1;
          4) ALTER TABLE t RENAME COLUMN c1_newtype to c1;

          Show
          Bryan Pendleton added a comment - Knut Anders pointed out that it isn't necessary to rename column c1 to c1_oldtype. The technique can be simplified to: 1) ALTER TABLE t ADD COLUMN c1_newtype NEWTYPE; 2) UPDATE t SET c1_newtype = c1; 3) ALTER TABLE t DROP COLUMN c1; 4) ALTER TABLE t RENAME COLUMN c1_newtype to c1;
          Hide
          Bryan Pendleton added a comment -

          I think that the proposed workaround (adding a new column, populating it, dropping the old column, and then renaming the new column to the desired name) is adequate for now.

          Show
          Bryan Pendleton added a comment - I think that the proposed workaround (adding a new column, populating it, dropping the old column, and then renaming the new column to the desired name) is adequate for now.
          Hide
          Andrew McIntyre added a comment -

          This issue has been resolved for over a year with no further movement. Closing.

          Show
          Andrew McIntyre added a comment - This issue has been resolved for over a year with no further movement. Closing.

            People

            • Assignee:
              Unassigned
              Reporter:
              Bryan Pendleton
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development