Re: change column length, is it that hard? - Mailing list pgsql-novice

Well, I did not even try it, because the docs say:

ALTER TABLE [ ONLY ] name [ * ]
     ALTER [ COLUMN ] column { SET DEFAULT expression | DROP DEFAULT }
ALTER TABLE [ ONLY ] name [ * ]
     ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER TABLE [ ONLY ] name [ * ]
     ALTER [ COLUMN ] column SET STATISTICS integer
ALTER TABLE [ ONLY ] name [ * ]
     ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }

And there is no syntax to change a column length.

Ron

PS: I use postgresql 7.4. Page I looked at is:
     http://www.postgresql.org/docs/7.4/static/sql-altertable.html

Charley Tiggs wrote:
> What error did you get when you tried to change length of the column
> and what syntax did you use?  This method should have worked.
>
> Charley
>
> On Jul 30, 2005, at 1:37 PM, Ron Arts wrote:
>
>> Hi,
>>
>> I have a lot of postgresql databases running on remote locations
>> using identical schemas. They run 24x7.
>>
>> One of the tables contains a field username character varying(16)
>> that needs to become varying(40), so just a little longer.
>>
>> A simple 'alter table alter column ....' does not work so I tried
>> creating a new column, dropping the old, and renaming:
>>
>> dbse=# alter table contact add column tmp_user varchar(40);
>> ALTER TABLE
>> dbse=# update contact set tmp_user = username;
>> UPDATE 71
>> dbse=# alter table contact alter column tmp_user set default '';
>> ALTER TABLE
>> dbse=# alter table contact alter column tmp_user set not NULL;
>> ALTER TABLE
>> dbse=# alter table contact drop column username
>> dbse-# ;
>> NOTICE:  rule _RETURN on view ox_deps depends on table contact  column
>> username
>> NOTICE:  view ox_deps depends on rule _RETURN on view ox_deps
>> NOTICE:  rule _RETURN on view pptpusers depends on table contact
>> column username
>> NOTICE:  view pptpusers depends on rule _RETURN on view pptpusers
>> NOTICE:  rule _RETURN on view team_members depends on table contact
>> column username
>> NOTICE:  view team_members depends on rule _RETURN on view  team_members
>> ERROR:  cannot drop table contact column username because other
>> objects depend on it
>> HINT:  Use DROP ... CASCADE to drop the dependent objects too.
>>
>> Oh man, I cannot use drop column cascade, this is a live database.
>>
>> Googling led me to believe I should remove dependencies on the column,
>> then do my thin, and then recreate dependencies.
>>
>> Can anyone show me an example how to do this? Please note I did not
>> design this database, and my grasp of views and rules is almost zero.
>>
>> Thanks,
>> Ron Arts
>>
>>
>>
>> --
>> NeoNova BV, The Netherlands
>> Professional internet and VoIP solutions
>>
>> http://www.neonova.nl   Kruislaan 419              1098 VA Amsterdam
>> info: 020-5628292       servicedesk: 020-5628292   fax: 020-5628291
>>
>> The following disclamer applies to this email:
>> http://www.neonova.nl/maildisclaimer
>>
>

--
NeoNova BV, The Netherlands
Professional internet and VoIP solutions

http://www.neonova.nl   Kruislaan 419              1098 VA Amsterdam
info: 020-5628292       servicedesk: 020-5628292   fax: 020-5628291

The following disclamer applies to this email:
http://www.neonova.nl/maildisclaimer