db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1515) Provide ALTER TABLE functionality to change a column's data type
Date Thu, 19 Oct 2006 16:36:35 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1515?page=comments#action_12443572 ] 
            
Bryan Pendleton commented on DERBY-1515:
----------------------------------------

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. 

> Provide ALTER TABLE functionality to change a column's data type
> ----------------------------------------------------------------
>
>                 Key: DERBY-1515
>                 URL: http://issues.apache.org/jira/browse/DERBY-1515
>             Project: Derby
>          Issue Type: New Feature
>          Components: Documentation, SQL
>    Affects Versions: 10.1.1.0, 10.2.1.6, 10.1.2.1, 10.1.3.1
>            Reporter: Bryan Pendleton
>            Priority: Minor
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message