Re: Changing the type of a column in an already populated

Hi,

--On Donnerstag, 21. November 2002 23:45 -0500 Brian Minton
<bminton(at)efn(dot)org> wrote:

> David Pradier wrote:
>> Hi!
>>
>> I'd like to know if it is possible to change the type of a column to a
>> compatible one, in an already populated database.
>> For example, changing a column from varchar(20) to varchar(25) ?
>> I'm using postgresql 7.3rc1
>>
>> Thanks for your help.
>>
>> Best regards,
>> David
>
> dump the database, edit the dump file, and reload. of course, this is
> annonying on live data, but it works

Its more easy with a temporary or short lifetime table:

create table temp_table
as select * from oldtable;

drop oldtable;
create oldtable ( ...);
insert into oldtable
select * from temp_table;

You can cover this in a transcation and with luck nobody
will even notice it for the whole process :)
(Take care of referential integrity or stored functions as
they might reference a cached, e.g. OID based reference to
the old table)

Regards
Tino