Thread: change existing table definition

change existing table definition

From
Chuming Chen
Date:
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

From
Peter Eisentraut
Date:
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

From
Chuming Chen
Date:
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

From
Michael Fuhr
Date:
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

From
Johannes Lochmann
Date:
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

From
"Lee Wu"
Date:
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

From
"Martin Fandel"
Date:
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

From
"Martin Fandel"
Date:
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

From
Brad Nicholson
Date:
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

From
Chuming Chen
Date:
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
>>>
>>>
>
>
>
>
>


Re: change existing table definition

From
"Martin Fandel"
Date:
> 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


Re: change existing table definition

From
Chuming Chen
Date:
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