How to insert new column in front of exsiting column?

[LeiLi <le...@hotmail.com>]

Hello, 

I'm new in Derby.
I need to insert a new column in front of another existing column in a
table?
How can I do it?
I saw the "alter table" only insert the column to the end of the table,
right?

Thanks in advance.

Lei
-- 
View this message in context: http://www.nabble.com/How-to-insert-new-column-in-front-of-exsiting-column--tp23526656p23526656.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: How to insert new column in front of exsiting column?

[Rick Hillegas <Ri...@Sun.COM>]

You can declare a view on the base table which returns the columns in 
the order you need. DatabaseMetaData.getColumns() will return the view's 
columns in the desired order.

Hope this helps,
-Rick

Dag H. Wanvik wrote:
> Daniel Noll <da...@nuix.com> writes:
>
>   
>> Bryan Pendleton wrote:
>>     
>>>> I saw the "alter table" only insert the column to the end of the table,
>>>>         
>>> Yes, but you can retrieve your columns in any order. For example:
>>> create table t (a int, b int, c int, d int);
>>> alter table t add column e int;
>>>       
>> What if they're using some kind of application which reads the
>> database metadata and automatically generates a form based on the
>> columns present in the table?
>>     
>
> You could use a temporary table, copy the data there, drop the
> original table and recreate it with the new column with a suitable
> default and put back the data in the old columns like this:
>
>   insert into t2 (oldcol1, oldcol2, ..) select * from tmptab;
>
> Dag
>   

Re: How to insert new column in front of exsiting column?

["Dag H. Wanvik" <Da...@Sun.COM>]

Daniel Noll <da...@nuix.com> writes:

> Bryan Pendleton wrote:
>>> I saw the "alter table" only insert the column to the end of the table,
>> Yes, but you can retrieve your columns in any order. For example:
>> create table t (a int, b int, c int, d int);
>> alter table t add column e int;
>
> What if they're using some kind of application which reads the
> database metadata and automatically generates a form based on the
> columns present in the table?

You could use a temporary table, copy the data there, drop the
original table and recreate it with the new column with a suitable
default and put back the data in the old columns like this:

  insert into t2 (oldcol1, oldcol2, ..) select * from tmptab;

Dag

Re: How to insert new column in front of exsiting column?

[Daniel Noll <da...@nuix.com>]

Bryan Pendleton wrote:
>> I saw the "alter table" only insert the column to the end of the table,
> 
> Yes, but you can retrieve your columns in any order. For example:
> 
> create table t (a int, b int, c int, d int);
> alter table t add column e int;

What if they're using some kind of application which reads the database 
metadata and automatically generates a form based on the columns present 
in the table?

Daniel


-- 
Daniel Noll                            Forensic and eDiscovery Software
Senior Developer                              The world's most advanced
Nuix                                                email data analysis
http://nuix.com/                                and eDiscovery software

Re: How to insert new column in front of exsiting column?

[Bryan Pendleton <bp...@amberpoint.com>]

> I saw the "alter table" only insert the column to the end of the table,

Yes, but you can retrieve your columns in any order. For example:

create table t (a int, b int, c int, d int);
alter table t add column e int;

select a,b,c,d,e from t; -- this is fine
select e,a,d,c,b from t; -- this is fine, too

thanks,

bryan