Re: ALTER Table (another)

Quoting Andrew Ayers <aayers@eldocomp.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

Network Administrator wrote:

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.

Hmm - I defer to the opinion of those with experience in DB engine
design (an area in which I have none), so your explanation seems probable...

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 am wondering why, if this is a problem, only let the user go from
smaller to larger. Or, if going in the opposite direction, if they try
to stuff something larger into the new smaller field, give an error
(similar to an invalid datatype kind of error). I would think,
regardless of whether the person is going from smaller to larger, or
larger to smaller, that they know what fields are affected, what
scripts/procedures need to be changed, what needs to be changed in the
app, etc - and all this needs to happen before going live with the new
database. If they are making such changes on a live database with no
regard for proper data handling/backup procedures, they deserve whatever
problems they get.

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)

Like I said, there is a workaround, but it seems kludgy to perform (and
what stops a user from "accidentally" doing something wrong here?
Nothing - and potentially, the disaster could be made even worse if they
don't have a clue what they are doing while altering the SQL to rebuild
the table, after they have already dropped it)...

Hopefully, either this change can come about in the future, or at least
a clear and consise explanation can be provided as to why it can't or
won't be implemented...

Thank you,

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.