pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
change existing table definition
11+ messages / 7 participants
[nested] [flat]

* change existing table definition
@ 2005-06-28 13:27 Chuming Chen <chen@musc.edu>
  2005-06-28 14:14 ` Re: change existing table definition Peter Eisentraut <peter_e@gmx.net>
  2005-06-28 14:26 ` Re: change existing table definition Johannes Lochmann <j.lochmann@i-penta.at>
  2005-06-28 14:31 ` Re: change existing table definition Michael Fuhr <mike@fuhr.org>
  2005-06-28 15:29 ` Re: change existing table definition Martin Fandel <martin.fandel@alphyra-evs.de>
  2005-06-28 15:59 ` Re: change existing table definition Martin Fandel <martin.fandel@alphyra-evs.de>
  2005-06-28 20:04 ` Re: change existing table definition Martin Fandel <martin.fandel@alphyra-evs.de>
  0 siblings, 6 replies; 11+ messages in thread

From: Chuming Chen @ 2005-06-28 13:27 UTC (permalink / raw)
  To: pgsql-admin

Hi, all,

How can I change the column definition of an existing table, ie. from 
varchar(30) to varchar(50)? Is there any way to add a new column to an 
existing table?

Thank you for your suggestions.

Chuming Chen



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: change existing table definition
  2005-06-28 13:27 change existing table definition Chuming Chen <chen@musc.edu>
@ 2005-06-28 14:14 ` Peter Eisentraut <peter_e@gmx.net>
  2005-06-28 14:39   ` Re: change existing table definition Chuming Chen <chen@musc.edu>
  5 siblings, 1 reply; 11+ messages in thread

From: Peter Eisentraut @ 2005-06-28 14:14 UTC (permalink / raw)
  To: chen@musc.edu; +Cc: pgsql-admin

Chuming Chen wrote:
> How can I change the column definition of an existing table, ie. from
> varchar(30) to varchar(50)? Is there any way to add a new column to
> an existing table?

The ALTER TABLE command can do all that.  You need version 8.0 or later 
for some functionality though.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: change existing table definition
  2005-06-28 13:27 change existing table definition Chuming Chen <chen@musc.edu>
  2005-06-28 14:14 ` Re: change existing table definition Peter Eisentraut <peter_e@gmx.net>
@ 2005-06-28 14:39   ` Chuming Chen <chen@musc.edu>
  2005-06-28 15:28     ` Re: change existing table definition Brad Nicholson <bnichols@ca.afilias.info>
  0 siblings, 1 reply; 11+ messages in thread

From: Chuming Chen @ 2005-06-28 14:39 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-admin

Peter Eisentraut wrote:

>Chuming Chen wrote:
>  
>
>>How can I change the column definition of an existing table, ie. from
>>varchar(30) to varchar(50)? Is there any way to add a new column to
>>an existing table?
>>    
>>
>
>The ALTER TABLE command can do all that.  You need version 8.0 or later 
>for some functionality though.
>
>  
>
Is there another way to do it in 7.* ?



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: change existing table definition
  2005-06-28 13:27 change existing table definition Chuming Chen <chen@musc.edu>
  2005-06-28 14:14 ` Re: change existing table definition Peter Eisentraut <peter_e@gmx.net>
  2005-06-28 14:39   ` Re: change existing table definition Chuming Chen <chen@musc.edu>
@ 2005-06-28 15:28     ` Brad Nicholson <bnichols@ca.afilias.info>
  0 siblings, 0 replies; 11+ messages in thread

From: Brad Nicholson @ 2005-06-28 15:28 UTC (permalink / raw)
  To: pgsql-admin

Chuming Chen wrote:

> Peter Eisentraut wrote:
>
>> Chuming Chen wrote:
>>  
>>
>>> How can I change the column definition of an existing table, ie. from
>>> varchar(30) to varchar(50)? Is there any way to add a new column to
>>> an existing table?
>>>   
>>
>>
>> The ALTER TABLE command can do all that.  You need version 8.0 or 
>> later for some functionality though.
>>
>>  
>>
> Is there another way to do it in 7.* ?
>

http://www.postgresql.org/docs/faqs.FAQ.html#4.3

> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: change existing table definition
  2005-06-28 13:27 change existing table definition Chuming Chen <chen@musc.edu>
@ 2005-06-28 14:26 ` Johannes Lochmann <j.lochmann@i-penta.at>
  5 siblings, 0 replies; 11+ messages in thread

From: Johannes Lochmann @ 2005-06-28 14:26 UTC (permalink / raw)
  To: pgsql-admin

On Tuesday 28 June 2005 15:27, Chuming Chen wrote:

Hello,

> How can I change the column definition of an existing table, ie. from
> varchar(30) to varchar(50)? 

You did not mention any version, so for 8.0.x:

alter table t alter col type varchar(50);

> Is there any way to add a new column to an 
> existing table?

alter table t add colum foo varchar(127)

Link tip:

http://www.postgresql.org/docs/8.0/interactive/ddl-alter.html

HTH

Johannes



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: change existing table definition
  2005-06-28 13:27 change existing table definition Chuming Chen <chen@musc.edu>
@ 2005-06-28 14:31 ` Michael Fuhr <mike@fuhr.org>
  5 siblings, 0 replies; 11+ messages in thread

From: Michael Fuhr @ 2005-06-28 14:31 UTC (permalink / raw)
  To: Chuming Chen <chen@musc.edu>; +Cc: pgsql-admin

On Tue, Jun 28, 2005 at 09:27:32AM -0400, Chuming Chen wrote:
> 
> How can I change the column definition of an existing table, ie. from 
> varchar(30) to varchar(50)? Is there any way to add a new column to an 
> existing table?

See ALTER TABLE in the documentation and "How do you change a
column's data type?" in the FAQ:

http://www.postgresql.org/docs/8.0/static/sql-altertable.html
http://www.postgresql.org/docs/faqs.FAQ.html#4.3

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: change existing table definition
  2005-06-28 13:27 change existing table definition Chuming Chen <chen@musc.edu>
@ 2005-06-28 15:29 ` Martin Fandel <martin.fandel@alphyra-evs.de>
  5 siblings, 0 replies; 11+ messages in thread

From: Martin Fandel @ 2005-06-28 15:29 UTC (permalink / raw)
  To: chen@musc.edu; +Cc: pgsql-admin

Hi

ALTER TABLE is only in PostgreSQL 8. But you can create a new table 
with varchar(50) and copy the data from the existing into the new 
table. How much relation_size has your table? Do you create the
dbsize-functions which are included in the contrib package?

Best regards,
Martin

Am Dienstag, den 28.06.2005, 10:39 -0400 schrieb Chuming Chen:
> Peter Eisentraut wrote:
> 
> >Chuming Chen wrote:
> >  
> >
> >>How can I change the column definition of an existing table, ie. from
> >>varchar(30) to varchar(50)? Is there any way to add a new column to
> >>an existing table?
> >>    
> >>
> >
> >The ALTER TABLE command can do all that.  You need version 8.0 or later 
> >for some functionality though.
> >
> >  
> >
> Is there another way to do it in 7.* ?
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: change existing table definition
  2005-06-28 13:27 change existing table definition Chuming Chen <chen@musc.edu>
@ 2005-06-28 15:59 ` Martin Fandel <martin.fandel@alphyra-evs.de>
  2005-06-28 17:26   ` Re: change existing table definition Chuming Chen <chen@musc.edu>
  5 siblings, 1 reply; 11+ messages in thread

From: Martin Fandel @ 2005-06-28 15:59 UTC (permalink / raw)
  To: chen@musc.edu; +Cc: pgsql-admin

Aehm sorry. ALTER TABLE is not only in PostgreSQL 8 ;). Only the
datatypes can be changed in postgres 8 (right?). I answered to fast.
I'm sorry.

CREATE TABLE newtable ( "bla" varchar(50));
CREATE INDEX/TRIGGER/... (with different names as the production table)
INSERT INTO newtable (select * from production);
ALTER TABLE production RENAME TO old;
ALTER TABLE newtable RENAME TO production;

If this is working correctly, you can drop the old INDEXES and 
rename them.

If its not working correctly 

ALTER TABLE production RENAME TO new;
ALTER TABLE old RENAME TO production;

and insert the different data (which is in the oldtable) into the
production table.

Greetings,
Martin


Am Dienstag, den 28.06.2005, 17:29 +0200 schrieb Martin Fandel:
> Hi
> 
> ALTER TABLE is only in PostgreSQL 8. But you can create a new table 
> with varchar(50) and copy the data from the existing into the new 
> table. How much relation_size has your table? Do you create the
> dbsize-functions which are included in the contrib package?
> 
> Best regards,
> Martin
> 
> Am Dienstag, den 28.06.2005, 10:39 -0400 schrieb Chuming Chen:
> > Peter Eisentraut wrote:
> > 
> > >Chuming Chen wrote:
> > >  
> > >
> > >>How can I change the column definition of an existing table, ie. from
> > >>varchar(30) to varchar(50)? Is there any way to add a new column to
> > >>an existing table?
> > >>    
> > >>
> > >
> > >The ALTER TABLE command can do all that.  You need version 8.0 or later 
> > >for some functionality though.
> > >
> > >  
> > >
> > Is there another way to do it in 7.* ?
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: change existing table definition
  2005-06-28 13:27 change existing table definition Chuming Chen <chen@musc.edu>
  2005-06-28 15:59 ` Re: change existing table definition Martin Fandel <martin.fandel@alphyra-evs.de>
@ 2005-06-28 17:26   ` Chuming Chen <chen@musc.edu>
  0 siblings, 0 replies; 11+ messages in thread

From: Chuming Chen @ 2005-06-28 17:26 UTC (permalink / raw)
  To: Martin Fandel <martin.fandel@alphyra-evs.de>; +Cc: pgsql-admin

Hi,

Thanks a lot for your quick reply and help. The following is what I find 
from google.  Will it work?

A quicker solution would be to use the pg_dump command
to dump the table, change the needed columns and restore
everything.

pg_dump -c -t <table name> <database> > <dumpfile>
psql <database> < <dumpfile>


Regards,

Chuming


Martin Fandel wrote:

>Aehm sorry. ALTER TABLE is not only in PostgreSQL 8 ;). Only the
>datatypes can be changed in postgres 8 (right?). I answered to fast.
>I'm sorry.
>
>CREATE TABLE newtable ( "bla" varchar(50));
>CREATE INDEX/TRIGGER/... (with different names as the production table)
>INSERT INTO newtable (select * from production);
>ALTER TABLE production RENAME TO old;
>ALTER TABLE newtable RENAME TO production;
>
>If this is working correctly, you can drop the old INDEXES and 
>rename them.
>
>If its not working correctly 
>
>ALTER TABLE production RENAME TO new;
>ALTER TABLE old RENAME TO production;
>
>and insert the different data (which is in the oldtable) into the
>production table.
>
>Greetings,
>Martin
>
>
>Am Dienstag, den 28.06.2005, 17:29 +0200 schrieb Martin Fandel:
>  
>
>>Hi
>>
>>ALTER TABLE is only in PostgreSQL 8. But you can create a new table 
>>with varchar(50) and copy the data from the existing into the new 
>>table. How much relation_size has your table? Do you create the
>>dbsize-functions which are included in the contrib package?
>>
>>Best regards,
>>Martin
>>
>>Am Dienstag, den 28.06.2005, 10:39 -0400 schrieb Chuming Chen:
>>    
>>
>>>Peter Eisentraut wrote:
>>>
>>>      
>>>
>>>>Chuming Chen wrote:
>>>> 
>>>>
>>>>        
>>>>
>>>>>How can I change the column definition of an existing table, ie. from
>>>>>varchar(30) to varchar(50)? Is there any way to add a new column to
>>>>>an existing table?
>>>>>   
>>>>>
>>>>>          
>>>>>
>>>>The ALTER TABLE command can do all that.  You need version 8.0 or later 
>>>>for some functionality though.
>>>>
>>>> 
>>>>
>>>>        
>>>>
>>>Is there another way to do it in 7.* ?
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>>      
>>>
>
>
>
>  
>




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: change existing table definition
  2005-06-28 13:27 change existing table definition Chuming Chen <chen@musc.edu>
@ 2005-06-28 20:04 ` Martin Fandel <martin.fandel@alphyra-evs.de>
  5 siblings, 0 replies; 11+ messages in thread

From: Martin Fandel @ 2005-06-28 20:04 UTC (permalink / raw)
  To: chen@musc.edu; +Cc: pgsql-admin

> pg_dump -c -t <table name> <database> > <dumpfile>
> psql <database> < <dumpfile>

I don't tested this but i think this works. 

Be dangerous with the "-c" Option of dump ;). After
the dump was created, new data could be inserted into the
database. If you dump in the file, all dumped tables are
dropped. It's better to RENAME the existing table and then
COPY in the data.

greetings,

Martin


Am Dienstag, den 28.06.2005, 13:26 -0400 schrieb Chuming Chen:
> pgsql-admin@postgresql.org




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: change existing table definition
@ 2005-06-28 15:34 Lee Wu <Lwu@mxlogic.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Lee Wu @ 2005-06-28 15:34 UTC (permalink / raw)
  To: chen@musc.edu; Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-admin

In PG 7, you can add column like:
create table test (c1 varchar(30));
alter table test add column c2 int;
select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

I do not know you can alter field/column length in PG 8 and it can be
done in PG 8:
select version();
                                                 version
------------------------------------------------------------------------
----------------------------------
 PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)
(1 row)
alter table test alter c1 type varchar(50);

I think in PG, you can:
1. add a new right length column like shown
2. update test set new_column=old_column;
3. alter table drop old_column


-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Chuming Chen
Sent: Tuesday, June 28, 2005 8:40 AM
To: Peter Eisentraut
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] change existing table definition

Peter Eisentraut wrote:

>Chuming Chen wrote:
>  
>
>>How can I change the column definition of an existing table, ie. from
>>varchar(30) to varchar(50)? Is there any way to add a new column to
>>an existing table?
>>    
>>
>
>The ALTER TABLE command can do all that.  You need version 8.0 or later

>for some functionality though.
>
>  
>
Is there another way to do it in 7.* ?

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2005-06-28 20:04 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-28 13:27 change existing table definition Chuming Chen <chen@musc.edu>
2005-06-28 14:14 ` Peter Eisentraut <peter_e@gmx.net>
2005-06-28 14:39   ` Chuming Chen <chen@musc.edu>
2005-06-28 15:28     ` Brad Nicholson <bnichols@ca.afilias.info>
2005-06-28 14:26 ` Johannes Lochmann <j.lochmann@i-penta.at>
2005-06-28 14:31 ` Michael Fuhr <mike@fuhr.org>
2005-06-28 15:29 ` Martin Fandel <martin.fandel@alphyra-evs.de>
2005-06-28 15:59 ` Martin Fandel <martin.fandel@alphyra-evs.de>
2005-06-28 17:26   ` Chuming Chen <chen@musc.edu>
2005-06-28 20:04 ` Martin Fandel <martin.fandel@alphyra-evs.de>
2005-06-28 15:34 Re: change existing table definition Lee Wu <Lwu@mxlogic.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