Re: ALTER Table (another)

From: Network Administrator <netadmin(at)vcsn(dot)com>
To: Andrew Ayers <aayers(at)eldocomp(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: ALTER Table (another)
Date: 2003-11-01 13:56:54
Message-ID: 1067695014.3fa3bba679ea0@webmail.vcsn.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Quoting Andrew Ayers <aayers(at)eldocomp(dot)com>:

> Shridhar Daithankar wrote:
> > Gaetano Mendola wrote:
> >
> >> Shridhar Daithankar wrote:
> >>
> >>> Alex wrote:
> >>>
> >>>> is it possible to add a column to a table at a specific place ?
> >>>> Reading the man pages i could not figure that out.
>
> [snip]
>
> On a similar note, it would be nice if you could do an ALTER TABLE to
> update the field definitions. Recently, I had the need to change the
> size of a VARCHAR field from 5 characters to 10 characters. This was due
> to a change in the underlying application, one that at the time of
> adding the field to the table was thought to be adequate - well, times
> and ideas change, users clamor for something different, etc...
>
> The only way to do what I needed done was to dump the table, drop it,
> alter the sql in the dump, then re-import it. While it didn't take long
> to do, it seemed like a big band-aid - something you should be able to
> do with an ALTER TABLE, but can't (I am currently using PG 7.3.2)...
>
> Is this something coming down the pipeline in the future?
>
> Andrew Ayers
> Phoenix, Arizona
>
> -- CONFIDENTIALITY NOTICE --
>
> This message is intended for the sole use of the individual and entity to
> whom it is addressed, and may contain information that is privileged,
> confidential and exempt from disclosure under applicable law. If you are not
> the intended addressee, nor authorized to receive for the intended addressee,
> you are hereby notified that you may not use, copy, disclose or distribute to
> anyone the message or any information contained in the message. If you have
> received this message in error, please immediately advise the sender by reply
> email, and delete the message. Thank you.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>

Andrew,

This was discussed on list a couple of months ago and I forget the outcome but I
think the gist of things what that there would be no **easy way** to maintain
ACID properties with this type of alter table function.

Though is would be fine to change a varchar 5 to 10 what about the person who
accidentally does the reverse? Now consider the logic to would go to making
sure this type of operation would not lead to corruption.

I didn't get a chance to find and re-read the previous thread on this but based
on what I remember I'm pretty sure this alter table function is on the todo list
it just did't make it into 7.4. If it is, its not in the dev docs
(http://developer.postgresql.org/docs/postgres/sql-altertable.html)

--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Network Administrator 2003-11-01 14:13:06 Re: database speed
Previous Message Martijn van Oosterhout 2003-11-01 13:33:41 Re: PG_RESTORE/DUMP Question