change existing table definition
change existing table definition
От:
Chuming Chen <chen@musc.edu>
Дата:
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
Re: change existing table definition
От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
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/
Re: change existing table definition
От:
Chuming Chen <chen@musc.edu>
Дата:
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
Re: change existing table definition
От:
Michael Fuhr <mike@fuhr.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/
Re: change existing table definition
От:
Johannes Lochmann <j.lochmann@i-penta.at>
Дата:
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
Re: change existing table definition
От:
"Lee Wu" <Lwu@mxlogic.com>
Дата:
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
Re: change existing table definition
От:
"Martin Fandel" <martin.fandel@alphyra-evs.de>
Дата:
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
Re: change existing table definition
От:
"Martin Fandel" <martin.fandel@alphyra-evs.de>
Дата:
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
Re: change existing table definition
От:
Brad Nicholson <bnichols@ca.afilias.info>
Дата:
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.
Re: change existing table definition
От:
Chuming Chen <chen@musc.edu>
Дата:
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
Re: change existing table definition
От:
"Martin Fandel" <martin.fandel@alphyra-evs.de>
Дата:
> pg_dump -c -t
Re: change existing table definition
От:
Chuming Chen <chen@musc.edu>
Дата:
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