DROP COLUMN ??

DROP COLUMN ??

Post by Michael Peter » Sat, 26 Jul 1997 04:00:00



possibly a stupid question - but how can I drop a column from a table? I
couldn't find any clue on this anywhere in the help files. The column that
I want to drop is not part of any constraint or index.

Michael Peters

 
 
 

DROP COLUMN ??

Post by Erland Sommarsk » Sun, 27 Jul 1997 04:00:00



Quote:>possibly a stupid question - but how can I drop a column from a table? I
>couldn't find any clue on this anywhere in the help files. The column that
>I want to drop is not part of any constraint or index.

sp_rename tbl, old_tbl
CREATE TABLE tbl ( /* new definition */)
INSERT tbl (....) SELECT ..... FROM #tmp
Migrate referencing foreign keys.
Restore table's own foreign keys, triggers and indexes.
DROP TABLE old_tbl

Apparently there are database tools out there that can generate code
for this kind of stuff. On my own I have some Perl code to help md to
do such things, for instance migrating referencing foreign keys from
the old table to the new.

--

F=F6r =F6vrigt anser jag att QP b=F6r f=F6rst=F6ras.
B=65sid=65s, I think QP should b=65 d=65stroy=65d.

 
 
 

DROP COLUMN ??

Post by Stephen Petschula » Tue, 29 Jul 1997 04:00:00



> possibly a stupid question - but how can I drop a column from a table?
> I
> couldn't find any clue on this anywhere in the help files. The column
> that I want to drop is not part of any constraint or index.

Last time I checked this was not possible. The work around is to create
a temp table with the column removed, import the orignal data into it
(minus the one column), delete the original table, and rename the temp.
Does anyone know if there is a third party tool that will automate this?

- steve

--
Stephen Petschulat
OpenRoad Communications
http://www.oroad.com/

 
 
 

DROP COLUMN ??

Post by Erland Sommarsk » Sun, 03 Aug 1997 04:00:00



Quote:>* Dropping a column should be efficient. If your database is large you
>will not appreciate the idea of unloading and reloading your data. Your
>data should be maintained where it is. This problem does not go away
>simply because a third-party tool tries to hide it from you.

>In my opinion, these are the kinds of issues that will decide whether an
>RDBMS is ready for prime-time or not.

As Per earns his daily bread working with Oracle Rdb (previously DEC/Rdb),
which has this feature, I can understand his position.

However, not all databases are designed in the same way. There were the
days when I worked with Rdb, but it's far too long ago for me to remember
how column drops might have been handled internally.

But for a column drop to be effcient with SQL Server at least, you cannot
really reclaim the space if the table is huge - you will have to reorganize
the table. What you can do is drop it from the system catalogues, so that
new rows will not have it. But then you must be able to handle that a table
may have dropped columns which would require some rewritings in the internals
of SQL Server.

In any case, if you drop a column and the space is not reclaimed you
probably get quite disappointed, so it may be better to have no command
at all, and leave the user to do it himself. This is also of importance
if you prefer space-reclaiming, a casual user will not throw away a
DROP COLUMN that will lock the table for hours.

--

F=F6r =F6vrigt anser jag att QP b=F6r f=F6rst=F6ras.
B=65sid=65s, I think QP should b=65 d=65stroy=65d.

 
 
 

1. 7.3 and HEAD broken for dropped columns of dropped types

I first thought that Andy Lewis' recent complaint might be a GIST
problem, but it's not.  Observe the following:

regression=# create domain mytype as int;
CREATE DOMAIN
regression=# create table foo (f1 int, f2 mytype);
CREATE TABLE
regression=# drop type mytype cascade;
NOTICE:  Drop cascades to table foo column f2
DROP TYPE
-- so far so good, but:
regression=# insert into foo values(1);
ERROR:  Unable to look up type id 703560
regression=# update foo set f1=1;
ERROR:  Unable to look up type id 703560

The failure occurs in ExecTypeFromTL(), which is required to build a
tuple descriptor for the output tuple of each plan node.  In these
cases, there is an output column (which will be NULL) for the dropped
column foo.f2 ... so the code goes off to get the type properties.
Oops.

Fortunately, we are not up the proverbial creek with no paddle, because
the only things we really need to know about the dropped column are its
typlen and typalign --- which just happen to still be recorded in
pg_attribute.attlen and attalign.  (Let's hear it for denormalization.)
It will take a little bit of code rearrangement to make that information
available to ExecTypeFromTL(), but I see no alternative.

I am thinking that it might be a good idea for ALTER TABLE DROP COLUMN
to reset atttypid to zero in the dropped column's pg_attribute row.
This would help catch any other places that are depending on a dropped
column's atttypid to still be valid.  On the other hand, it would
possibly confuse applications that are looking at pg_attribute.
Comments anyone?

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

2. how to display a picture in ktml document from Oracle

3. Problem with DROP COLUMN and ALTER COLUMN

4. using function in select statement and passing parameters to it

5. could not drop column MyColumn from table MyTable Error 207: Invalid Column Name

6. Goldmine 4.0 & SQL

7. Dropping column silently kills multi-coumn index (was [ODBC] Error when accessing tables with deleted columns)

8. SQL Statement

9. Drop Column

10. dropping columns (short question)

11. DROP COLUMN

12. How do you drop columns from a table