Postgres Pro
Downloads
Home   >   mailing lists

Thread: change column length, is it that hard?

change column length, is it that hard?

From
Ron Arts
Date:
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
Attachment

Re: change column length, is it that hard?

From
Tom Lane
Date:
Ron Arts <ron.arts@neonova.nl> writes:
> One of the tables contains a field username character varying(16)
> that needs to become varying(40), so just a little longer.

The traditional solution to this is to hack the pg_attribute.atttypmod
value for the column.  I *strongly* recommend practicing on a scratch
database before you do this for real --- wrapping the whole thing in a
transaction so you can roll it back is a good idea, too.

> 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

If you've got dependent views then you need to think about whether their
output column types need to change too.

            regards, tom lane

Re: change column length, is it that hard?

From
Ron Arts
Date:
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
Attachment

qucik timestamp questions

From
Date:
i read through the manual and googled, but i couldn't
find the answer to the following questions...

if i insert now() into a timestamp field, will it
insert the local time or the gmt time?  i think it is
the local time, but i want to be sure before i set up
my table.

is the only reason for selection "timestamp with time
zone" to be able to display the time zone in the
future?

tia...

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

timestamp timezone problem

From
Date:
when i run the following query

select now()::timestamptz

in pgadmin3's sql window, it ends with -07.  as i
understand it, that is the time zone.  my computer is
GMT-8 since i'm in the pacific time zone.

why is there a discrepancy - or is it just me not
knowing the nuts and bolts of what is going on?

to tz or not tz? that is the question.  i think i want
to tz b/c i can't guarantee that tz won't become
relevant in the future (ie, data may be entered from
two different time zones a report may want to compare
the dates).

any advice would be appreciated.

tia...



____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs

Re: timestamp timezone problem

From
Afton & Ray Still
Date:
----- Original Message -----
From: <operationsengineer1@yahoo.com>
To: <pgsql-novice@postgresql.org>
Sent: Monday, August 01, 2005 11:29 AM
Subject: [NOVICE] timestamp timezone problem


> when i run the following query
>
> select now()::timestamptz
>
> in pgadmin3's sql window, it ends with -07.  as i
> understand it, that is the time zone.  my computer is
> GMT-8 since i'm in the pacific time zone.

Daylight Savings?  (One hour eariler than normal)

>
> why is there a discrepancy - or is it just me not
> knowing the nuts and bolts of what is going on?
>
> to tz or not tz? that is the question.  i think i want
> to tz b/c i can't guarantee that tz won't become
> relevant in the future (ie, data may be entered from
> two different time zones a report may want to compare
> the dates).
>
> any advice would be appreciated.
>
> tia...
>
>
>
> ____________________________________________________
> Start your day with Yahoo! - make it your home page
> http://www.yahoo.com/r/hs
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.338 / Virus Database: 267.9.7/60 - Release Date: 7/28/2005
>
>


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.7/60 - Release Date: 7/28/2005

Re: timestamp timezone problem

From
John DeSoi
Date:
On Aug 1, 2005, at 1:29 PM, <operationsengineer1@yahoo.com>
<operationsengineer1@yahoo.com> wrote:

> select now()::timestamptz
>
> in pgadmin3's sql window, it ends with -07.  as i
> understand it, that is the time zone.  my computer is
> GMT-8 since i'm in the pacific time zone.
>
> why is there a discrepancy - or is it just me not
> knowing the nuts and bolts of what is going on?

Daylight savings time:

show timezone;
TimeZone
----------
EST5EDT
(1 row)

EDT = Eastern Daylight Time, otherwise it would be EST = Eastern
Standard Time.



>
> to tz or not tz? that is the question.  i think i want
> to tz b/c i can't guarantee that tz won't become
> relevant in the future (ie, data may be entered from
> two different time zones a report may want to compare
> the dates).


This is exactly the reason to include the time zone -- if it will be
important to compare time generated from more than one time zone. So
the need for this depends on your application.


> if i insert now() into a timestamp field, will it
> insert the local time or the gmt time?  i think it is
> the local time, but i want to be sure before i set up
> my table.

Local time. Try it yourself with

select now()::timestamp;
             now
----------------------------
2005-08-01 18:55:21.211502
(1 row)






John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL