pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedHow do I drop a column from a table?
8+ messages / 5 participants
[nested] [flat]
* How do I drop a column from a table?
@ 1999-08-30 18:46 Robert Carbonari <robertC@opmr.com>
1999-08-31 07:02 ` Re: [INTERFACES] How do I drop a column from a table? Thomas Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 1 reply; 8+ messages in thread
From: Robert Carbonari @ 1999-08-30 18:46 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] How do I drop a column from a table?
1999-08-30 18:46 How do I drop a column from a table? Robert Carbonari <robertC@opmr.com>
@ 1999-08-31 07:02 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-08-31 13:59 ` Re: [INTERFACES] How do I drop a column from a table? Tatsuo Ishii <t-ishii@sra.co.jp>
0 siblings, 1 reply; 8+ messages in thread
From: Thomas Lockhart @ 1999-08-31 07:02 UTC (permalink / raw)
To: robertC@opmr.com; +Cc: pgsql-interfaces
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] How do I drop a column from a table?
1999-08-30 18:46 How do I drop a column from a table? Robert Carbonari <robertC@opmr.com>
1999-08-31 07:02 ` Re: [INTERFACES] How do I drop a column from a table? Thomas Lockhart <lockhart@alumni.caltech.edu>
@ 1999-08-31 13:59 ` Tatsuo Ishii <t-ishii@sra.co.jp>
1999-08-31 14:21 ` Re: [INTERFACES] How do I drop a column from a table? Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-08-31 14:39 ` Re: [INTERFACES] How do I drop a column from a table? Rusty Brooks <rbrooks@utdallas.edu>
0 siblings, 2 replies; 8+ messages in thread
From: Tatsuo Ishii @ 1999-08-31 13:59 UTC (permalink / raw)
To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: robertC@opmr.com; pgsql-interfaces
> > 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] How do I drop a column from a table?
1999-08-30 18:46 How do I drop a column from a table? Robert Carbonari <robertC@opmr.com>
1999-08-31 07:02 ` Re: [INTERFACES] How do I drop a column from a table? Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-08-31 13:59 ` Re: [INTERFACES] How do I drop a column from a table? Tatsuo Ishii <t-ishii@sra.co.jp>
@ 1999-08-31 14:21 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1 sibling, 0 replies; 8+ messages in thread
From: Thomas Lockhart @ 1999-08-31 14:21 UTC (permalink / raw)
To: t-ishii@sra.co.jp; robertC@opmr.com; +Cc: pgsql-interfaces
> > 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] How do I drop a column from a table?
1999-08-30 18:46 How do I drop a column from a table? Robert Carbonari <robertC@opmr.com>
1999-08-31 07:02 ` Re: [INTERFACES] How do I drop a column from a table? Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-08-31 13:59 ` Re: [INTERFACES] How do I drop a column from a table? Tatsuo Ishii <t-ishii@sra.co.jp>
@ 1999-08-31 14:39 ` Rusty Brooks <rbrooks@utdallas.edu>
1999-08-31 19:06 ` Re: [INTERFACES] How do I drop a column from a table? Hannu Krosing <hannu@trust.ee>
1 sibling, 1 reply; 8+ messages in thread
From: Rusty Brooks @ 1999-08-31 14:39 UTC (permalink / raw)
To: Tatsuo Ishii <t-ishii@sra.co.jp>; +Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; robertC@opmr.com; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] How do I drop a column from a table?
1999-08-30 18:46 How do I drop a column from a table? Robert Carbonari <robertC@opmr.com>
1999-08-31 07:02 ` Re: [INTERFACES] How do I drop a column from a table? Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-08-31 13:59 ` Re: [INTERFACES] How do I drop a column from a table? Tatsuo Ishii <t-ishii@sra.co.jp>
1999-08-31 14:39 ` Re: [INTERFACES] How do I drop a column from a table? Rusty Brooks <rbrooks@utdallas.edu>
@ 1999-08-31 19:06 ` Hannu Krosing <hannu@trust.ee>
0 siblings, 0 replies; 8+ messages in thread
From: Hannu Krosing @ 1999-08-31 19:06 UTC (permalink / raw)
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
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] How do I drop a column from a table?
@ 1999-08-31 14:34 Robert Carbonari <robertC@opmr.com>
0 siblings, 0 replies; 8+ messages in thread
From: Robert Carbonari @ 1999-08-31 14:34 UTC (permalink / raw)
To: pgsql-interfaces
> 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?
Hi Robert,
I don't know how to email all in elm, so if you could forward this to the
list
for verification I would appreciate.
Anyway, I don't think you can drop a column. What you can do is create a
new table by doing a select into the new table (leaving out the columns you
don't want) and then droping the old table. Once the old table is dropped
you can rename the new to the old one's name:
select f1,f2...,fn into table newtable from oldtable;
drop table oldtable;
alter table newtable rename to oldtable;
Hope this helps...james
> ************
>
Robert Carbonari
Programmer
Optimal Robotics Corp.
e-mail: robertc@optimal-robotics.com
phone: (514)738-8885 ext. 146
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: [INTERFACES] How do I drop a column from a table?
@ 1999-08-31 14:46 Robert Carbonari <robertC@opmr.com>
0 siblings, 0 replies; 8+ messages in thread
From: Robert Carbonari @ 1999-08-31 14:46 UTC (permalink / raw)
To: rbrooks@utdallas.edu, Tatsuo Ishii <t-ishii@sra.co.jp>; +Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; pgsql-interfaces
Thank you all very much!
> -----Original Message-----
> From: Rusty Brooks [SMTP:rbrooks@utdallas.edu]
> Sent: Tuesday, August 31, 1999 10:40 AM
> To: Tatsuo Ishii
> Cc: Thomas Lockhart; robertC@opmr.com; pgsql-interfaces@postgreSQL.org
> Subject: Re: [INTERFACES] How do I drop a column from a table?
>
> 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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~1999-08-31 19:06 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-30 18:46 How do I drop a column from a table? Robert Carbonari <robertC@opmr.com>
1999-08-31 07:02 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-08-31 13:59 ` Tatsuo Ishii <t-ishii@sra.co.jp>
1999-08-31 14:21 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-08-31 14:39 ` Rusty Brooks <rbrooks@utdallas.edu>
1999-08-31 19:06 ` Hannu Krosing <hannu@trust.ee>
1999-08-31 14:34 Re: [INTERFACES] How do I drop a column from a table? Robert Carbonari <robertC@opmr.com>
1999-08-31 14:46 RE: [INTERFACES] How do I drop a column from a table? Robert Carbonari <robertC@opmr.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox