Thread: ALTER TABLE & COLUMN
Hi, I am surprised by the fact that altering tables and columns is so limited in PostgreSQL, this powerful DBMS! Changing columntypes and droping columns are missing important features I think. I am corious about the rationale behind these design decisions. Are they high-end features for a DBMS? And if they are beingplanned to be included in a future version, in which version and when if it's possible, they will be implemented? Thanks for that powerful DBMS; it is superb on all other aspects! Cagil SEKER Software Engineer BizNet Information Technology Systems and Consulting
On Fri, 2002-11-29 at 11:05, Çağıl Şeker wrote: > I am surprised by the fact that altering tables and columns is so > limited in PostgreSQL, this powerful DBMS! Changing column types and > droping columns are missing important features I think. 7.3 (released yesterday) supports DROP COLUMN, as well as a bunch of other enhancements to ALTER TABLE. As for changing the type of a column, how would this be anything more than syntactic sugar over renaming the existing column to a temp name, adding a new column with the appropriate type & name, moving the data from the old column to the new one, and dropping the old one? Since a lot of data type changes are not obvious (how do you convert the data from one type to another, in all cases?), I don't see a real problem leaving this in the hands of the admin. The one exception might be changing the length limit on a varchar(n) column, but that's such a small case I'm not sure it's worth the bother (and can be done by hacking the system catalogs anyway). Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
On Fri, Nov 29, 2002 at 03:22:06PM -0500, Neil Conway wrote: > Since a lot of data type changes are not obvious (how do you convert the > data from one type to another, in all cases?), I don't see a real > problem leaving this in the hands of the admin. The one exception might > be changing the length limit on a varchar(n) column, but that's such a > small case I'm not sure it's worth the bother (and can be done by > hacking the system catalogs anyway). I agree with you that generally changing datatypes is a task best left to the DBA, but in case of CHAR/VARCHAR length and probably NUMERIC length/precision it would be very nice to have some kind of "syntactic sugar". Figuring out NUMERIC's atttypmod is not what I'd call trivial, let alone change it. Sure, it may be for you, but... -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Los dioses no protegen a los insensatos. Éstos reciben protección de otros insensatos mejor dotados" (Luis Wu, Mundo Anillo)
Date: 29 Nov 2002 15:22:06 -0500 From: Neil Conway <neilc@samurai.com> > > > I am surprised by the fact that altering tables and columns is so > > limited in PostgreSQL, this powerful DBMS! Changing column types and > > droping columns are missing important features I think. > Actually this seems to be one of the rare areas where MySQL is better. PG 7.3 improves a lot though. > As for changing the type of a column, how would this be anything more > than syntactic sugar over renaming the existing column to a temp name, > adding a new column with the appropriate type & name, moving the data > from the old column to the new one, and dropping the old one? > Is this latter procedure safe with respect to triggers? Or is it necessary to recompile all triggers on the table? The latter would be particularly cumbersume with PostgreSQL as there is no ALTER TRIGGER RECOMPILE statement like in Oracle. > Since a lot of data type changes are not obvious (how do you convert the > data from one type to another, in all cases?), I don't see a real > problem leaving this in the hands of the admin. > This assumes that the database admin and the application develloper are the same person. In general this assumption is wrong and they are often even employed by different companies so that the admin cannot be bothered with stupid schema changes and the application develloper never gets DBA privileges. So please keep on improving the possibility to make schema changes with ALTER commands. Christoph Dalitz
> -----Original Message----- > From: Christoph Dalitz [mailto:christoph.dalitz@hs-niederrhein.de] > > Date: 29 Nov 2002 15:22:06 -0500 > From: Neil Conway <neilc@samurai.com> > > > > > I am surprised by the fact that altering tables and columns is so > > > limited in PostgreSQL, this powerful DBMS! Changing > column types and > > > droping columns are missing important features I think. > > > Actually this seems to be one of the rare areas where MySQL is better. > PG 7.3 improves a lot though. I've started a new project a few weeks ago. I had to make a choice between these two dbs. Just because table and column administrationis difficult on PG, I chose MySQL (although I'd found PG better on all other areas). Then at that time PG 7.3comes out and solves at least some of the problems! What a coincedence! Now I will switch to PG. > > > As for changing the type of a column, how would this be > anything more > > than syntactic sugar over renaming the existing column to a > temp name, > > adding a new column with the appropriate type & name, > moving the data > > from the old column to the new one, and dropping the old one? > > > Is this latter procedure safe with respect to triggers? > Or is it necessary to recompile all triggers on the table? > The latter would be particularly cumbersume with PostgreSQL as there > is no ALTER TRIGGER RECOMPILE statement like in Oracle. > That's a huge problem really, not a syntactic sugar, otherwise more of a syntactic salt :) I want some of my users (not all are db admin) to be able to alter tables with pgAdmin. How can I expect them to achievethose N-step voodoo as it will be seen to them? > > Since a lot of data type changes are not obvious (how do > you convert the > > data from one type to another, in all cases?), I don't see a real > > problem leaving this in the hands of the admin. > > > This assumes that the database admin and the application > develloper are the > same person. In general this assumption is wrong and they are > often even employed > by different companies so that the admin cannot be bothered > with stupid schema > changes and the application develloper never gets DBA privileges. > This one is also true when we deal with bigger projects, like I will start in a month. I think PG should consider that "small"administration problem. PG deserves this. > So please keep on improving the possibility to make schema > changes with ALTER commands. > > Christoph Dalitz > >
Hi Cagil, --On Montag, 2. Dezember 2002 09:13 +0200 Çagil Seker <cagils@biznet.com.tr> wrote: >> -----Original Message----- >> From: Christoph Dalitz [mailto:christoph.dalitz@hs-niederrhein.de] >> >> Date: 29 Nov 2002 15:22:06 -0500 >> From: Neil Conway <neilc@samurai.com> >> > >> > > I am surprised by the fact that altering tables and columns is so >> > > limited in PostgreSQL, this powerful DBMS! Changing >> column types and >> > > droping columns are missing important features I think. >> > >> Actually this seems to be one of the rare areas where MySQL is better. >> PG 7.3 improves a lot though. Without triggers and referential integrity, changing columns is a lot easyer ;) > I've started a new project a few weeks ago. I had to make a choice > between these two dbs. Just because table and column administration is > difficult on PG, I chose MySQL (although I'd found PG better on all other > areas). Then at that time PG 7.3 comes out and solves at least some of > the problems! What a coincedence! Now I will switch to PG. ... *snip* ... > That's a huge problem really, not a syntactic sugar, otherwise more of a > syntactic salt :) I want some of my users (not all are db admin) to be > able to alter tables with pgAdmin. How can I expect them to achieve those > N-step voodoo as it will be seen to them? I dont think its wise to make regular changes to your table structure. Let alone these changes are made by untrained people. Regards Tino
Tino Wildenhain wrote: > >> Actually this seems to be one of the rare areas where MySQL is better. > >> PG 7.3 improves a lot though. > > Without triggers and referential integrity, changing columns is a > lot easyer ;) And if you want to be able to roll it back in an aborted transaction. Many commerical databases don't even get this one right. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>>>>> "NC" == Neil Conway <neilc@samurai.com> writes: NC> problem leaving this in the hands of the admin. The one exception might NC> be changing the length limit on a varchar(n) column, but that's such a NC> small case I'm not sure it's worth the bother (and can be done by NC> hacking the system catalogs anyway). That's a PITA to do, and scares the willies out of mere mortals, myself included. Doing a add column, rename old, copy data, rename new is also a PITA, and I'm not sure the effects of that on foreign keys that my reference that column. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
Neil Conway writes:
> As for changing the type of a column, how would this be anything more
> than syntactic sugar over renaming the existing column to a temp name,
> adding a new column with the appropriate type & name, moving the data
> from the old column to the new one, and dropping the old one?
I agree with you that it is "just" syntactic sugar, but I like sweets ;-). For
example, from 7.2 to 7.3 we got DROP COLUMN which I think is really nice. In
the 7.2 documentation there is this example, how to "drop" a column:
CREATE TABLE temp AS SELECT did, city FROM distributors;
DROP TABLE distributors;
CREATE TABLE distributors (
did DECIMAL(3) DEFAULT 1,
name VARCHAR(40) NOT NULL
);
INSERT INTO distributors SELECT * FROM temp;
DROP TABLE temp;
But users like me (and I guess even some admins) think "uuhhh, how do I do
this with a table with 150 columns, what happens to the triggers, I can make
so many errors and screw things up...". Now we have the syntactic glue and I
can tell the db to drop one out of the 150 columns and the chance that I mess
up the database are much smaller than before.
> Since a lot of data type changes are not obvious (how do you convert the
> data from one type to another, in all cases?), I don't see a real
> problem leaving this in the hands of the admin. The one exception might
> be changing the length limit on a varchar(n) column, but that's such a
> small case I'm not sure it's worth the bother (and can be done by
> hacking the system catalogs anyway).
What about this:
If the conversion can be done with a normal built in Postgresql cast, let's
use that one, e.g. from text to integer use the "normal" cast. If the user
wants some strange stuff, he has to define a conversion function. If we have
something non-obvious like circle -> integer, we need a user supplied
function. The syntax could be something like:
ALTER TABLE sometable
ALTER COLUMN somecolumn SET TYPE TO integer USE FUNCTION circle2int
(maybe without the TO and USING instead of USE)
Then each element of the new column would be set to new = circle2int(old).
Just an idea...
Cheers
Tilo