Thread: How do I drop a column from a table?

Hi,

I'm still quite new at this but it should be a simple operation:  How do I
drop a column form a Postgres table.  The next best thing would be knowing
how to change a column so that it can accept null values.

Any takers?

Thanks

Robert Carbonari
Programmer
Optimal Robotics Corp.
e-mail: robertc@optimal-robotics.com
phone:  (514)738-8885 ext. 146

> I'm still quite new at this but it should be a simple operation:  How do I
> drop a column form a Postgres table.  The next best thing would be knowing
> how to change a column so that it can accept null values.

Adding columns is directly supported. Removing columns, or redefining
them, is done with a CREATE TABLE AS/DROP TABLE/CREATE TABLE AS/DROP
TABLE sequence, where you write the columns you want to retain into a
temporary table, delete the original table, and then do the same back
into a new table with the same name as the original.

Good luck.
                        - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

> > I'm still quite new at this but it should be a simple operation:  How do I
> > drop a column form a Postgres table.  The next best thing would be knowing
> > how to change a column so that it can accept null values.
> 
> Adding columns is directly supported. Removing columns, or redefining
> them, is done with a CREATE TABLE AS/DROP TABLE/CREATE TABLE AS/DROP
> TABLE sequence, where you write the columns you want to retain into a
> temporary table, delete the original table, and then do the same back
> into a new table with the same name as the original.

But this method might lose some characteristics such as primary key,
unique constraints, no?
---
Tatsuo Ishii

> > Adding columns is directly supported. Removing columns, or redefining
> > them, is done with a CREATE TABLE AS/DROP TABLE/CREATE TABLE AS/DROP
> > TABLE sequence, where you write the columns you want to retain into a
> > temporary table, delete the original table, and then do the same back
> > into a new table with the same name as the original.
> But this method might lose some characteristics such as primary key,
> unique constraints, no?

OK, then CREATE TABLE AS/DROP TABLE/CREATE TABLE/INSERT/DROP TABLE,
where the CREATE TABLE can include all relevant characteristics.

btw, Tatsuo reminds me that if you have a SERIAL type, you will need
to retain the underlying sequence which supports it, define the column
in the new table as an INT, and define the DEFAULT clause for it to
reference the sequence explicitly.
                       - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

On Tue, 31 Aug 1999, Tatsuo Ishii wrote:

==>But this method might lose some characteristics such as primary key,
==>unique constraints, no?

I'm not sure, but if it does you can add them back with unique indexes.  I
also think you can add these attributes back with an alter table combined
with a CONTSTRAINT x PRIMARYKEY(col)
 ~~~~~~ |    |         Rusty Brooks |c--OD         The University of Texas at Dallas |    _)        Computer Science
Dept.|    |           |-.  |         http://www.utdallas.edu/~rbrooks/   `-#  /A
 
/ /_|..`#.J/     Statisticians probably do it.

Better by far you should forget and smile than that you should remember
and be sad.

If a picture is worth a thousand words, then television must be worth
30,000 words per second.-- Rusty Brooks

Rusty Brooks wrote:
> 
> On Tue, 31 Aug 1999, Tatsuo Ishii wrote:
> 
> ==>But this method might lose some characteristics such as primary key,
> ==>unique constraints, no?
> 
> I'm not sure, but if it does you can add them back with unique indexes.  I
> also think you can add these attributes back with an alter table combined
> with a CONTSTRAINT x PRIMARYKEY(col)

AFAIK PostgreSQL dos not support most of the ALTER TABLE functionality, 
only ADD COLUMN and RENAME COLUMN and RENAME are supported ;(

ALTERing, ADDing and DROPping of constraints is not supported, as is not 
disbling indexes. There is a workaround for UNIQUE constraint, which is 
implemented using UNIQUE INDEXes anyway and thus can be manipulated by 
DROP/CREATE index, but you can't change the NULL/NOT NULL constraint, 
at least without directly manipulating system tables.

Also any changing of types is not allowed (Oracle allows type changes of 
INT -> FLOAT, NUMERIC->CHAR and of shorter (VAR)CHAR types to longer
ones)

Btw, UNIQUE constraint is implemented in a way that does not allow
duplicates 
even inside transactions:

hannu=> create table uni(
hannu-> i int primary key
hannu-> );
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index uni_pkey
for table uni
CREATE
hannu=> insert into uni values(1);
INSERT 2517548 1
hannu=> insert into uni values(2);
INSERT 2517549 1
hannu=> update uni set i=i+1;
ERROR:  Cannot insert a duplicate key into a unique index

I think this is the same type of flaw that discouraged Vadim from 
implementing FOREIGN KEYs based on the code from contrib/

So there is a long way to go to acieve more or less dynamic table
manipulation;
currently table defs are mostly write-only.

-----
Hannu