Postgres Pro
Downloads
Home   >   mailing lists

Thread: alter table workaround

alter table workaround

From
wsheldah@lexmark.com
Date:

I just had to expand a column from varchar(10) to varchar(75). The affected
table had a sequence and almost a dozen referential integrity triggers, so a
straight 'drop table; create table' solution wouldn't be enough.  Joel Burton's
techdoc on the subject
(http://techdocs.postgresql.org/techdocs/updatingcolumns.php) suggests usging
pg_dump to dump the entire database, then change the create table statement for
the table, then drop and restore the entire database.

What I'm wondering is whether it would be safe to just dump the affected table
with `pg_dump -t mytable mydb >mytable.sql`, and then in psql do:
drop table mytable;
drop sequence mysequence;
\i mytable.sql

...after editing mytable.sql of course.  My goal here is to only drop and
restore what I really need to.  Will this restore all affected triggers, etc.
that are affected by the drop table command?  Any other caveats?

Thanks,

Wes Sheldahl

Re: alter table workaround

From
Stephan Szabo
Date:
On Wed, 31 Oct 2001 wsheldah@lexmark.com wrote:

> What I'm wondering is whether it would be safe to just dump the affected table
> with `pg_dump -t mytable mydb >mytable.sql`, and then in psql do:
> drop table mytable;
> drop sequence mysequence;
> \i mytable.sql
>
> ...after editing mytable.sql of course.  My goal here is to only drop and
> restore what I really need to.  Will this restore all affected triggers, etc.
> that are affected by the drop table command?  Any other caveats?

It depends.  If this table is referenced by other tables (for example)
I don't think that will re-add the constraint to the other table.  There
may be issues like that that you'd run into.

Re: alter table workaround

From
"Aasmund Midttun Godal"
Date:
Well, the foreign keys are dumped out as separate statements, so I think it will work. It's definitely worth a try...

Regards,

Aasmund.

On Wed, 31 Oct 2001 08:27:10 -0800 (PST), Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
>
> On Wed, 31 Oct 2001 wsheldah@lexmark.com wrote:
>
>
> It depends.  If this table is referenced by other tables (for example)
> I don't think that will re-add the constraint to the other table.  There
> may be issues like that that you'd run into.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46

Re: alter table workaround

From
Stephan Szabo
Date:
On Wed, 31 Oct 2001, Aasmund Midttun Godal wrote:

> Well, the foreign keys are dumped out as separate statements, so I
> think it will work. It's definitely worth a try...

It'll only write out part of the constraint in those cases I believe
(and it seems to be borne out by my 7.2 pg_dump output).  It looks like it
writes out the triggers for the table that's specified, but not for the
other side of the constraint, but when you do the drop table, all of the
constraints are removed.  The restore after a drop table would probably
be a partially functioning constraint.

Re: alter table workaround

From
Tom Lane
Date:
wsheldah@lexmark.com writes:
> I just had to expand a column from varchar(10) to varchar(75).

If you want a quick hack, rather than a general-purpose solution,
it'd suffice to change the atttypmod value of the appropriate row
in pg_attribute.  Something along the line of

    update pg_attribute set atttypmod = 75 + 4
    where attname = 'columnname' and
    attrelid = (select oid from pg_class where relname = 'tablename');

Untested but I think it's right --- make a backup first (or else don't
blame me if you break your database ;-))

            regards, tom lane