Skip site navigation (1) Skip section navigation (2)

Re: change column length, is it that hard?

From: Ron Arts <ron(dot)arts(at)neonova(dot)nl>
To: Charley Tiggs <charley(at)xpressdocs(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: change column length, is it that hard?
Date: 2005-07-30 21:30:33
Message-ID: 42EBF179.3000607@neonova.nl (view raw or flat)
Thread:
Lists: 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

In response to

pgsql-novice by date

Next:From: Dane EnsignDate: 2005-07-31 02:16:16
Subject:
Previous:From: Tom LaneDate: 2005-07-30 20:19:45
Subject: Re: change column length, is it that hard?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group