Sender: hannu@kodu.home.ee
Message-ID: <37CC27B1.D88C7CEA@trust.ee>
Date: Tue, 31 Aug 1999 22:06:25 +0300
From: Hannu Krosing <hannu@trust.ee>
Organization: Trust-O-Matic =?iso-8859-1?Q?O=DC?=
MIME-Version: 1.0
To: Rusty Brooks <rbrooks@utdallas.edu>
CC: Tatsuo Ishii <t-ishii@sra.co.jp>,
	Thomas Lockhart <lockhart@alumni.caltech.edu>, robertC@opmr.com,
	pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] How do I drop a column from a table?
References: <Pine.LNX.4.10.9908310935550.8672-100000@pickles>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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