Re: Adding a New Column Specifically In a Table

From: Thom Brown <thom(at)linux(dot)com>
To: rod(at)iol(dot)ie
Cc: Carlos Mennens <carlos(dot)mennens(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Adding a New Column Specifically In a Table
Date: 2010-10-14 10:32:12
Message-ID: AANLkTikWJvnG3VsG3g2_vPC9HC00jLwfAWZnt9_wkjOw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 13 October 2010 23:19, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:
> On 13/10/2010 19:04, Carlos Mennens wrote:
>>
>> OK so I have read the docs and Google to try and find a way to add a
>> new column to an existing table. My problem is I need this new column
>> to be created 3rd  rather than just dumping this new column to the end
>> of my table. I can't find anywhere how I can insert my new column as
>> the 3rd table column rather than the last (seventh). Does anyone know
>> how I can accomplish this or if it's even possible. Seems like a
>> common task but I checked the documentation and may have missed it in
>> my reading.
>>
>
> It isn't possible at the moment. This has come up a good bit in the past, so
> you'll find debate in the archives...
>
> One work-around is to add the column, and then do:
>
>  create table new_table as
>    select [columns in desired order] from old_table;
>
>  drop table old_table;
>
>  alter table new_table rename to old_table;
>
> ...remembering to deal with foreign key constraints as you go.

..and indexes, triggers, rules, views and every other dependency.
It's a potentially tricky solution to something which shouldn't really
be a problem.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Raymond O'Donnell 2010-10-14 11:07:38 Re: Adding a New Column Specifically In a Table
Previous Message ghatpande 2010-10-14 10:13:34 Re: Adding a New Column Specifically In a Table