From: | Hannu Krosing <hannu(at)trust(dot)ee> |
---|---|
To: | Rusty Brooks <rbrooks(at)utdallas(dot)edu> |
Cc: | Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp>, Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>, robertC(at)opmr(dot)com, pgsql-interfaces(at)postgreSQL(dot)org |
Subject: | Re: [INTERFACES] How do I drop a column from a table? |
Date: | 1999-08-31 19:06:25 |
Message-ID: | 37CC27B1.D88C7CEA@trust.ee |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-interfaces |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Mount | 1999-08-31 19:39:10 | Re: [INTERFACES] [INTERFACE] Need an example |
Previous Message | gotzdx | 1999-08-31 18:22:23 | LQQK 6519 |