Thread: change existing table definition
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
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/
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
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/
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
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
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
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
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.
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 >>> >>> > > > > >
> 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
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