How to change column type in PostgreSQL 7.1.2
Greetings,
I'm wondering if there is a way to change a column type in PostgreSQL
7.1.2 without reconstruction of table + pg_dump/restore of table data
(machine can't be stopped for a long time). I have a table with a integer
column and I would like to change it to varchar(20). One more thing, this
collumn belongs to a composed primary key. Is it possible?
Best regards
----------------------------------------------------------------------------
----
Jos� Vilson de Mello de Farias
Software Engineer
D�gitro Tecnologia Ltda - www.digitro.com.br
APC - Customer Oriented Applications
E-mail: vilson.farias@digitro.com.br
Tel.: +55 48 281 7158
ICQ 11866179
I don't know about the primary key part.. what I do is create a table identical to the one I'm copying (minus index's, primary keys, stuff like that) and copy my data over.. verify the data was copied.. then drop the original.. recreate the original the way you want.. then copy the data back over.. and verify.. then drop the temp table..
Travis
-----Original Message-----
From: Vilson farias [mailto:vilson.farias@digitro.com.br]
Sent: Thursday, June 12, 2003 7:54 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to change column type in PostgreSQL 7.1.2
Greetings,
I'm wondering if there is a way to change a column type in PostgreSQL
7.1.2 without reconstruction of table + pg_dump/restore of table data
(machine can't be stopped for a long time). I have a table with a integer
column and I would like to change it to varchar(20). One more thing, this
collumn belongs to a composed primary key. Is it possible?
Best regards
----------------------------------------------------------------------------
----
José Vilson de Mello de Farias
Software Engineer
Dígitro Tecnologia Ltda - www.digitro.com.br
APC - Customer Oriented Applications
E-mail: vilson.farias@digitro.com.br
Tel.: +55 48 281 7158
ICQ 11866179
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Import Notes
Resolved by subject fallback
alter table t add column newcolumn varchar(20);
update t set newcolumn = oldcolumn::varchar;
-- drop constraints / indexes
alter table t drop column oldcolumn;
alter table t rename newcolumn to oldcolumn;
-- create new constraints / indexes
vacuum analyze t;
/M
----- Original Message -----
From: "Williams, Travis L, NPONS" <tlw@att.com>
To: "Vilson farias" <vilson.farias@digitro.com.br>; <pgsql-general@postgresql.org>
Sent: Thursday, June 12, 2003 3:56 PM
Subject: Re: [GENERAL] How to change column type in PostgreSQL 7.1.2
Show quoted text
I don't know about the primary key part.. what I do is create a table identical to the one I'm copying (minus index's, primary keys, stuff like that) and copy my data over.. verify the data was copied.. then drop the original.. recreate the original the way you want.. then copy the data back over.. and verify.. then drop the temp table..
Travis
-----Original Message-----
From: Vilson farias [mailto:vilson.farias@digitro.com.br]
Sent: Thursday, June 12, 2003 7:54 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to change column type in PostgreSQL 7.1.2Greetings,
I'm wondering if there is a way to change a column type in PostgreSQL
7.1.2 without reconstruction of table + pg_dump/restore of table data
(machine can't be stopped for a long time). I have a table with a integer
column and I would like to change it to varchar(20). One more thing, this
collumn belongs to a composed primary key. Is it possible?Best regards
----------------------------------------------------------------------------
----
José Vilson de Mello de Farias
Software EngineerDígitro Tecnologia Ltda - www.digitro.com.br
APC - Customer Oriented Applications
E-mail: vilson.farias@digitro.com.br
Tel.: +55 48 281 7158
ICQ 11866179---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
drop key, Add a column, fill from old column, delete old column, rename
new column to old name, add key.
Watch out for indexes, functions, views, and especially referential
constraints against that column. This is one reason why I always use a
surrogate key.
this Will only work if you have unique rows.
Vilson farias wrote:
Show quoted text
Greetings,
I'm wondering if there is a way to change a column type in PostgreSQL
7.1.2 without reconstruction of table + pg_dump/restore of table data
(machine can't be stopped for a long time). I have a table with a integer
column and I would like to change it to varchar(20). One more thing, this
collumn belongs to a composed primary key. Is it possible?Best regards
----------------------------------------------------------------------------
----
Jos� Vilson de Mello de Farias
Software EngineerD�gitro Tecnologia Ltda - www.digitro.com.br
APC - Customer Oriented Applications
E-mail: vilson.farias@digitro.com.br
Tel.: +55 48 281 7158
ICQ 11866179---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Please note that this is on 7.1.2, so the first thing Vilson should do is
upgrade to at least 7.2.4. THEN do all these things.
On Thu, 12 Jun 2003, Dennis Gearon wrote:
Show quoted text
drop key, Add a column, fill from old column, delete old column, rename
new column to old name, add key.Watch out for indexes, functions, views, and especially referential
constraints against that column. This is one reason why I always use a
surrogate key.this Will only work if you have unique rows.
Vilson farias wrote:
Greetings,
I'm wondering if there is a way to change a column type in PostgreSQL
7.1.2 without reconstruction of table + pg_dump/restore of table data
(machine can't be stopped for a long time). I have a table with a integer
column and I would like to change it to varchar(20). One more thing, this
collumn belongs to a composed primary key. Is it possible?Best regards
----------------------------------------------------------------------------
----
Jos� Vilson de Mello de Farias
Software EngineerD�gitro Tecnologia Ltda - www.digitro.com.br
APC - Customer Oriented Applications
E-mail: vilson.farias@digitro.com.br
Tel.: +55 48 281 7158
ICQ 11866179---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster