Thread: ALTER DROP COLUMN
neochi=> ALTER TABLE news DROP COLUMN date; ERROR: ALTER TABLE/DROP COLUMN not yet implemented What's that about??? How do I drop a field of a table?! Thanks, -Ross
I know it's a pain, but it isn't all that bad. Just rename your table like so: ALTER TABLE <old_name> RENAME TO <another_name> Then re-create the old table without the deleted column(s): SELECT col1, col2, col4 INTO TABLE <old_name> FROM <another_name> Then delete the old table: DROP TABLE <another_name> --- "ross::barna" <rbarna@neochi.com> wrote: > neochi=> ALTER TABLE news DROP COLUMN date; > ERROR: ALTER TABLE/DROP COLUMN not yet implemented > > > What's that about??? How do I drop a field of a table?! > > Thanks, > -Ross > __________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
ross::barna writes: > neochi=> ALTER TABLE news DROP COLUMN date; > ERROR: ALTER TABLE/DROP COLUMN not yet implemented > > > What's that about??? How do I drop a field of a table?! http://www.postgresql.org/docs/faq-english.html#4.5 -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
I just tried this recommended procedure with a little test. It copied over the Attribute and Type information and the primary key index, but left everything else behind: not-null constraints, defaults, references constraints, check constraints, sequence generated for 'serial' type -- to name a few. So how is this useful for anything beyond a toy database? This inability to drop a column makes it hard to follow an "extreme programming" style of development, where one designs only as much into the database schema as is needed at the moment (DTSTTCPW, YAGNI). When I do that I often find that I need to drop columns (in favor of other columns of different types) as I evolve the schema to support ever more complex applications. PostgreSQL makes this a bit harder than it needs to be. On Mon, Feb 12, 2001 at 12:34:20PM -0800, Brent R. Matzelle wrote: > I know it's a pain, but it isn't all that bad. Just rename your > table like so: > > ALTER TABLE <old_name> RENAME TO <another_name> > > Then re-create the old table without the deleted column(s): > > SELECT col1, col2, col4 INTO TABLE <old_name> FROM > <another_name> > > Then delete the old table: > > DROP TABLE <another_name> -- Fred Yankowski fred@OntoSys.com tel: +1.630.879.1312 Principal Consultant www.OntoSys.com fax: +1.630.879.1370 OntoSys, Inc 38W242 Deerpath Rd, Batavia, IL 60510, USA
Is there any chance of adding a note about this to the docs? It does not seem to be documented anywhere! Maybe a note in ALTER TABLE (sql-altertable.htm) and changing the FAQ slightly to mention what happen to triggers, views, constraints etc. Regards Ben > -----Original Message----- > From: Fred Yankowski [mailto:fred@ontosys.com] > Sent: 12 February 2001 21:41 > Cc: pgsql-general@postgresql.org > Subject: [GENERAL] Re: ALTER DROP COLUMN > > > I just tried this recommended procedure with a little test. It copied > over the Attribute and Type information and the primary key index, but > left everything else behind: not-null constraints, defaults, > references constraints, check constraints, sequence generated for > 'serial' type -- to name a few. > > So how is this useful for anything beyond a toy database? > > This inability to drop a column makes it hard to follow an "extreme > programming" style of development, where one designs only as much into > the database schema as is needed at the moment (DTSTTCPW, YAGNI). > When I do that I often find that I need to drop columns (in favor of > other columns of different types) as I evolve the schema to support > ever more complex applications. PostgreSQL makes this a bit harder > than it needs to be. > > On Mon, Feb 12, 2001 at 12:34:20PM -0800, Brent R. Matzelle wrote: > > I know it's a pain, but it isn't all that bad. Just rename your > > table like so: > > > > ALTER TABLE <old_name> RENAME TO <another_name> > > > > > Then re-create the old table without the deleted column(s): > > > > SELECT col1, col2, col4 INTO TABLE <old_name> FROM > > <another_name> > > > > Then delete the old table: > > > > DROP TABLE <another_name> > > -- > Fred Yankowski fred@OntoSys.com tel: +1.630.879.1312 > Principal Consultant www.OntoSys.com fax: +1.630.879.1370 > OntoSys, Inc 38W242 Deerpath Rd, Batavia, IL 60510, USA >
>So how is this useful for anything beyond a toy database? > >This inability to drop a column makes it hard to follow an "extreme >programming" style of development, where one designs only as much into >the database schema as is needed at the moment (DTSTTCPW, YAGNI). >When I do that I often find that I need to drop columns (in favor of >other columns of different types) as I evolve the schema to support >ever more complex applications. PostgreSQL makes this a bit harder >than it needs to be. I would content that any moderately complex database design that starts at the keyboard instead of a pad of paper (i.e., DESIGNED) is going to have more problems than a "toy" database. Postgres is used in many "real" applications but more than likely they were thought out and designed before committing to tables. len morgan
"Len Morgan" <len-morgan@crcom.net> writes: > >So how is this useful for anything beyond a toy database? > > > >This inability to drop a column makes it hard to follow an "extreme > >programming" style of development, where one designs only as much into > >the database schema as is needed at the moment (DTSTTCPW, YAGNI). > >When I do that I often find that I need to drop columns (in favor of > >other columns of different types) as I evolve the schema to support > >ever more complex applications. PostgreSQL makes this a bit harder > >than it needs to be. > > I would content that any moderately complex database design that starts at > the keyboard instead of a pad of paper (i.e., DESIGNED) is going to have > more problems than a "toy" database. Postgres is used in many "real" > applications but more than likely they were thought out and designed before > committing to tables. For me, who has developed a few databases with web interfaces, this isn't very easy, because the end user isn't always (read: almost never) very good at explaining exactly what he/she wants, or they come up with things they want later, after they've used the system for a while. They, as users, don't realise that what seems to be small changes to them, might need some redesigning of the database. As a programmer, I can't tell the users that we can't make the change, because they should've thought of it from the start. Tomas
--- Tomas Berndtsson <tomas@nocrew.org> wrote: > "Len Morgan" <len-morgan@crcom.net> writes: > > > >So how is this useful for anything beyond a toy database? > > > > > >This inability to drop a column makes it hard to follow an > "extreme > > >programming" style of development, where one designs only > as much into > > >the database schema as is needed at the moment (DTSTTCPW, > YAGNI). > > >When I do that I often find that I need to drop columns (in > favor of > > >other columns of different types) as I evolve the schema to > support > > >ever more complex applications. PostgreSQL makes this a > bit harder > > >than it needs to be. > > > > I would content that any moderately complex database design > that starts at > > the keyboard instead of a pad of paper (i.e., DESIGNED) is > going to have > > more problems than a "toy" database. Postgres is used in > many "real" > > applications but more than likely they were thought out and > designed before > > committing to tables. > > For me, who has developed a few databases with web interfaces, > this > isn't very easy, because the end user isn't always (read: > almost > never) very good at explaining exactly what he/she wants, or > they come > up with things they want later, after they've used the system > for a > while. They, as users, don't realise that what seems to be > small > changes to them, might need some redesigning of the database. > As a > programmer, I can't tell the users that we can't make the > change, > because they should've thought of it from the start. > > > Tomas You might also try dumping the database, dropping and re-creating the database, and then hacking the dump to add, delete, alter columns. That way the original structure should remain intact. I have been in the same situation as you many times where users request alterations that can seriously screw up your original structure. However, it is your responsibility to inform them that any change, no matter how small can require a lot of development time. If they are requesting very frequent changes then the project should return to the design (paper) phase that Len spoke of. I know it's not as fun as poking around in the database, but it can save you loads of time and headaches. Brent __________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
On Tue, Feb 13, 2001 at 06:40:30AM -0800, Brent R. Matzelle wrote:
> You might also try dumping the database, dropping and
> re-creating the database, and then hacking the dump to add,
> delete, alter columns. That way the original structure should
> remain intact.
I find that the dump output is a pain to work with. Information that
was concise when first defined ("id_stuff serial primary key") appears
in least common denominator form as separate indexes, sequences,
constraints, etc. And if I'm deleting a column, do I have to write
Perl scripts or the like to strip out the obsolete column data from
the "COPY ... FROM" sections? What a PITA.
A key strategy in Extreme Programming is to make the
system-under-development as amenable to change as possible (while
satisfying current requirements as simply as possible -- an obvious
tension). Some projects may have the luxury of defining the schema
"on paper" once and for all, no changes ever. I've just never seen
such a project in over 20 years professional programming experience.
I'm not advocating _hacking_ a schema into shape, but I do want to
_evolve_ my systems as needed to satisfy my customers' evolving
business needs (and our joint understanding of such).
> I have been in the same situation as you many times where users
> request alterations that can seriously screw up your original
> structure. However, it is your responsibility to inform them
> that any change, no matter how small can require a lot of
> development time.
Or we can improve our tools so that changes aren't so painful to
undertake. PostgreSQL is a damn fine piece of work, and there are
ways that it too could evolve to make it an even more powerful tool
for doing what our customers need done, rather than just what is
convenient for us to do.
[I just got dizzy and fell off my high horse. Oww...]
--
Fred Yankowski fred@OntoSys.com tel: +1.630.879.1312
Principal Consultant www.OntoSys.com fax: +1.630.879.1370
OntoSys, Inc 38W242 Deerpath Rd, Batavia, IL 60510, USA
Fred Yankowski <fred@ontosys.com> writes:
> On Tue, Feb 13, 2001 at 06:40:30AM -0800, Brent R. Matzelle wrote:
> > You might also try dumping the database, dropping and
> > re-creating the database, and then hacking the dump to add,
> > delete, alter columns. That way the original structure should
> > remain intact.
>
[...]
>
> Or we can improve our tools so that changes aren't so painful to
> undertake. PostgreSQL is a damn fine piece of work, and there are
> ways that it too could evolve to make it an even more powerful tool
> for doing what our customers need done, rather than just what is
> convenient for us to do.
I agree with you; it would be very nice to be able to easily change
the schema in a running database. However, the developers of PG have
limited time, and they typically devote their efforts more towards
production issues than to development issues. In other words,
changing a schema is something the developer is going to want to do,
but it's not something you'll do much once your product is in
production.
Given the choice between having rock-solid production support and
having less solid production support, but better development support,
I'd still vote on the side of production. If somebody like you or me
wants to add ALTER TABLE DROP COLUMN, I'm sure the PG developers would
gladly accept a patch.
In the meantime, what works well for me is to build my schema by way
of an SQL script or three. When I change the schema, I just change
the script. If the database is in development, I can frequently get
away with dropping the whole thing, re-creating it, and re-running the
script. Even if not, I can run pg_dump, drop and re-create the
database, run the schema script, and then restore just the data (no
structure) from dump. It's a pain, but it does work. And it has the
added benefit of giving me a text record of the schema that I can
check into CVS and examine previous versions of.
Chris
--
chris@mt.sri.com -----------------------------------------------------
Chris Jones SRI International, Inc.
www.sri.com
--- Fred Yankowski <fred@ontosys.com> wrote:
> On Tue, Feb 13, 2001 at 06:40:30AM -0800, Brent R. Matzelle
> wrote:
> > You might also try dumping the database, dropping and
> > re-creating the database, and then hacking the dump to add,
> > delete, alter columns. That way the original structure
> should
> > remain intact.
>
> I find that the dump output is a pain to work with.
> Information that
> was concise when first defined ("id_stuff serial primary key")
> appears
> in least common denominator form as separate indexes,
> sequences,
> constraints, etc. And if I'm deleting a column, do I have to
> write
> Perl scripts or the like to strip out the obsolete column data
> from
> the "COPY ... FROM" sections? What a PITA.
Hey, you don't have to tell me that it's a pain. I was just
giving you another way to do the same thing.
<snip>
> [I just got dizzy and fell off my high horse. Oww...]
>
Everyone has to rant once and a while ;) Anyway, there is hope
in all of this. The PostgreSQL development team could build
support for it in some newer versions. And it's open source so
even you could hack the functionality it if you want. Hey, I'd
do it myself if I had the time and motivation. In the meantime
we'll just have to wait I guess.
Brent
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
On Tue, 13 Feb 2001, Fred Yankowski wrote: > Or we can improve our tools so that changes aren't so painful to > undertake. PostgreSQL is a damn fine piece of work, and there are > ways that it too could evolve to make it an even more powerful tool > for doing what our customers need done, rather than just what is > convenient for us to do. There have been some questions about how to best do a drop column which is a big reason there isn't a final implementation yet (although there is some implementation in the code I believe ifdefed out with _DROP_COLUMN_HACK__). There are discussions about this on -hackers a while back (sorry don't remember when).
> I would content that any moderately complex database design that starts at > the keyboard instead of a pad of paper (i.e., DESIGNED) is going to have > more problems than a "toy" database. Postgres is used in many "real" > applications but more than likely they were thought out and designed before > committing to tables. So you are able to design your "real" applications completely without any problems, huh? Never have a missing attribute; never an extra attribute; never an attribute in the wrong table; never having to split/normalize/denormalize for performance or other reasons, huh? You are amazing -- and a liar. David
> So you are able to design your "real" applications completely without any > problems, huh? Never have a missing attribute; never an extra attribute; > never an attribute in the wrong table; never having to > split/normalize/denormalize for performance or other reasons, > huh? Yes, it does occasionally happen that you want to drop a column. It can be worked around by dropping/reloading the table.. I personally would find it useful to have the ability to do it in a single command, no matter how slow/innefficient the implementation is. Just a matter of someone getting around to writing it, I think... - Andrew
>So you are able to design your "real" applications completely without any >problems, huh? Never have a missing attribute; never an extra attribute; >never an attribute in the wrong table; never having to >split/normalize/denormalize for performance or other reasons, huh? You are >amazing -- and a liar. It's nice to see that you're keeping this discussion on such a high level. I never said "never." Occationally I will have to add a field or two to accomodate a different requirement. When I have to remove a field, I use the procedure outlined above, and have kept the design documentation so that I can recreate the triggers/constraints/etc. for those fields that require it. I tend to do most of my triggers and constraints and such in my code, not in the database itself because of the current lack of an error number (at least in 7.0.2). I have to translate the response I get back to find out what the true problem was so I can act accordingly. That is being worked on. Personally, I'd trade a free select into/rename/drop for an Oracle drop column any day. Besides, where would I find such friendly conversation to help when I have a problem? Happy Valentine's Day. :-) len morgan