How to Alter a Column Datatype for Derby Database

How to alter a column datatype for derby database?

Here is the Derby SQL script to change column MY_TABLE.MY_COLUMN from BLOB(255) to BLOB(2147483647):

ALTER TABLE MY_TABLE ADD COLUMN NEW_COLUMN BLOB(2147483647);
UPDATE MY_TABLE SET NEW_COLUMN=MY_COLUMN;
ALTER TABLE MY_TABLE DROP COLUMN MY_COLUMN;
RENAME COLUMN MY_TABLE.NEW_COLUMN TO MY_COLUMN;

How to change datatype of the column in derby database?

hope below one will help.

ALTER TABLE Table_Name ALTER COLUMN Column_Name SET DATA TYPE NUMERIC(14,3);

Java DB (Derby) Database Alter Column Size

Solved:

ALTER TABLE [table] ALTER COLUMN [column] SET DATA TYPE [type];

I wasn't using the SET DATA TYPE function and was instead just trying to redeclare it by using ALTER COLUMN [column] [type];

alter data type of existing column from bigint to varchar in Apache derby

I think the first method would work with this change:

UPDATE Country SET LawID_NEW = TRIM(CHAR(LawID));

Alter table add column AFTER in derby database

I don't believe so. Column ordering isn't really a standard SQL feature. Well-written db applications shouldn't care about column ordering. You can specify the order of the columns in your output by naming the columns in your SQL statement as in:

create table t (a int, b int, c int );

select b, c, a from t;

How to alter column from PRIMARY KEY to IDENTITY for Derby

Looking at the documentation this seems impossible. You can change the type length (not even the type itself), the default, nullability and the next generated value but even the last option requires the column to already be defined as IDENTITY. A thread from 2009 says that you can't even add an IDENTITY column. A test confirms this is true to this day.

So it seems there is only one solution: You have to replace the table. Something like this:

1. create a new table with a placeholder name that contains the desired columns
2. copy any data over from the original table
3. drop the original table
4. rename the new table

It's really an unfortunate solution because if you already have other tables referencing the id column of your table as that would mean further work.

I tried messing with the system tables but they seem to be read-only (and for good reason).

---

Related Topics