Thread: how to select one column into another in same table?

Currently I can select one column into another with two statements:

     alter table <table_name> add column id_old int;
     update <table_name> set id_old = id;

Is there a way to do this in one statement with a select into?  I have
tried various select statements but want the new column (with the same
data) to be in the same table and to have it execute much more quickly
that the two statements currently do.

thanks


J.V.

On Tue, Oct 4, 2011 at 12:24 PM, J.V. <jvsrvcs@gmail.com> wrote:
> Currently I can select one column into another with two statements:
>
>    alter table <table_name> add column id_old int;
>    update <table_name> set id_old = id;
>
> Is there a way to do this in one statement with a select into?  I have tried
> various select statements but want the new column (with the same data) to be
> in the same table and to have it execute much more quickly that the two
> statements currently do.

Do you need another column or do you just want to alter a column that
already exists?  If so you can alter a column from one type to another
and throw a using clause at it to convert the data in some way.  I
think we need to know a bit better what you're trying to do.,

What I need to do is to save the id column for future use and then
modify the id column resetting all values from another sequence.

So I need to select the id column or somehow get the data into another
column in the same table.

And then I can update the id column (after dropping the constraint).

J.V.

On 10/4/2011 1:09 PM, Scott Marlowe wrote:
> On Tue, Oct 4, 2011 at 12:24 PM, J.V.<jvsrvcs@gmail.com>  wrote:
>> Currently I can select one column into another with two statements:
>>
>>     alter table<table_name>  add column id_old int;
>>     update<table_name>  set id_old = id;
>>
>> Is there a way to do this in one statement with a select into?  I have tried
>> various select statements but want the new column (with the same data) to be
>> in the same table and to have it execute much more quickly that the two
>> statements currently do.
> Do you need another column or do you just want to alter a column that
> already exists?  If so you can alter a column from one type to another
> and throw a using clause at it to convert the data in some way.  I
> think we need to know a bit better what you're trying to do.,
>

I don't this this is possible as postgres.

There is something simular with:
alter table table_name alter column column_foo using column_bar
But I don't think there's any performance advantage over a simple
update and the using clause doesn't appear to have an equivalent in an
add column statement.

You could.
alter table table_name rename column_foo to column_bar;
alter table table_name add column_foo foo_data_type default =
nextval('new_foo_sequence');

This has your best chance of success since renaming a column should
not have to touch every row of the table.

Regards

On 4 October 2011 20:21, J.V. <jvsrvcs@gmail.com> wrote:
> What I need to do is to save the id column for future use and then modify
> the id column resetting all values from another sequence.
>
> So I need to select the id column or somehow get the data into another
> column in the same table.
>
> And then I can update the id column (after dropping the constraint).
>
> J.V.
>
> On 10/4/2011 1:09 PM, Scott Marlowe wrote:
>>
>> On Tue, Oct 4, 2011 at 12:24 PM, J.V.<jvsrvcs@gmail.com>  wrote:
>>>
>>> Currently I can select one column into another with two statements:
>>>
>>>    alter table<table_name>  add column id_old int;
>>>    update<table_name>  set id_old = id;
>>>
>>> Is there a way to do this in one statement with a select into?  I have
>>> tried
>>> various select statements but want the new column (with the same data) to
>>> be
>>> in the same table and to have it execute much more quickly that the two
>>> statements currently do.
>>
>> Do you need another column or do you just want to alter a column that
>> already exists?  If so you can alter a column from one type to another
>> and throw a using clause at it to convert the data in some way.  I
>> think we need to know a bit better what you're trying to do.,
>>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>