FAQ
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

Search Discussions

  • Tom Lane at Jul 30, 2005 at 8:21 pm

    Ron Arts 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
  • Operationsengineer1 at Aug 1, 2005 at 5:18 pm
    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
  • Operationsengineer1 at Aug 1, 2005 at 5:29 pm
    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
  • Ron Arts at Jul 30, 2005 at 9:30 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJul 30, '05 at 6:37p
activeAug 1, '05 at 5:29p
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase