pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedURGENT!!! changing Column size
10+ messages / 9 participants
[nested] [flat]
* URGENT!!! changing Column size
@ 2003-10-27 17:10 mohan@physics.gmu.edu
0 siblings, 5 replies; 10+ messages in thread
From: mohan@physics.gmu.edu @ 2003-10-27 17:10 UTC (permalink / raw)
To: pgsql-sql
Hi can we change the size of a column in postgres. I have a table named
institution and column name is name varchar2(25), i want to change it to
varchar2(50). Please let me know.
--Mohan
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: URGENT!!! changing Column size
@ 2003-10-27 17:46 Tomasz Myrta <jasiek@klaster.net>
parent: mohan@physics.gmu.edu
4 siblings, 1 reply; 10+ messages in thread
From: Tomasz Myrta @ 2003-10-27 17:46 UTC (permalink / raw)
To: mohan@physics.gmu.edu; +Cc: pgsql-sql
Dnia 2003-10-27 18:10, Użytkownik mohan@physics.gmu.edu napisał:
> Hi can we change the size of a column in postgres. I have a table named
> institution and column name is name varchar2(25), i want to change it to
> varchar2(50). Please let me know.
alter table institution add column tmp varchar2(50);
update institution set tmp=name;
alter table institution drop column name;
alter table institution rename tmp to name;
(or something like this)
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: URGENT!!! changing Column size
@ 2003-10-27 17:48 CoL <col@mportal.hu>
parent: mohan@physics.gmu.edu
4 siblings, 0 replies; 10+ messages in thread
From: CoL @ 2003-10-27 17:48 UTC (permalink / raw)
To: pgsql-sql
Hi,
mohan@physics.gmu.edu wrote, On 10/27/2003 6:10 PM:
> Hi can we change the size of a column in postgres. I have a table named
> institution and column name is name varchar2(25), i want to change it to
> varchar2(50). Please let me know.
1 solution:
begin;
create temporary table temp as select * from mytable;
drop table mytable;
create table mytable (name varchar(50));
insert into mytable select CAST(name AS varchar(50)) from temp;
drop table temp;
commit;
C.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: URGENT!!! changing Column size
@ 2003-10-27 17:50 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: mohan@physics.gmu.edu
4 siblings, 0 replies; 10+ messages in thread
From: Gary Stainburn @ 2003-10-27 17:50 UTC (permalink / raw)
To: pgsql-sql
On Monday 27 Oct 2003 5:10 pm, mohan@physics.gmu.edu wrote:
> Hi can we change the size of a column in postgres. I have a table named
> institution and column name is name varchar2(25), i want to change it to
> varchar2(50). Please let me know.
>
> --Mohan
try
alter table institution add column newname varchar2(50);
update institution set newname = name;
alter table institution drop column namel;
alter table institution rename column newname to name;
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: URGENT!!! changing Column size
@ 2003-10-27 19:01 Tomasz Myrta <jasiek@klaster.net>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 10+ messages in thread
From: Tomasz Myrta @ 2003-10-27 19:01 UTC (permalink / raw)
To: btober@seaworthysys.com; +Cc: mohan@physics.gmu.edu; pgsql-sql
Dnia 2003-10-27 19:33, Użytkownik btober@seaworthysys.com napisał:
> I've seen these sets of steps suggested in response to other such
> inquires, but doesn't this break views on the associated table, or may
> just not work because if a view dependency exists?
It would be the second case (it won't work at all). You can use
"cascade" when dropping column. After this you need to recreate views
dropped together with a column.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: URGENT!!! changing Column size
@ 2003-10-28 13:00 Theodore Petrosky <tedpet5@yahoo.com>
parent: mohan@physics.gmu.edu
4 siblings, 1 reply; 10+ messages in thread
From: Theodore Petrosky @ 2003-10-28 13:00 UTC (permalink / raw)
To: mohan@physics.gmu.edu; pgsql-sql
why not just
pg_dump dbname > olddb.out
pico olddb.out
edit the section that defines the table
save and exit
dropdb dbname
createdb dbname
psql dbname < olddb.out
no fuss no muss...
Ted
--- mohan@physics.gmu.edu wrote:
> Hi can we change the size of a column in postgres. I
> have a table named
> institution and column name is name varchar2(25), i
> want to change it to
> varchar2(50). Please let me know.
>
> --Mohan
>
>
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
__________________________________
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
http://launch.yahoo.com/promos/britneyspears/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: URGENT!!! changing Column size
@ 2003-10-28 13:28 Franco Bruno Borghesi <franco@akyasociados.com.ar>
parent: Theodore Petrosky <tedpet5@yahoo.com>
0 siblings, 1 reply; 10+ messages in thread
From: Franco Bruno Borghesi @ 2003-10-28 13:28 UTC (permalink / raw)
To: Theodore Petrosky <tedpet5@yahoo.com>; +Cc: mohan@physics.gmu.edu; pgsql-sql
Dopping the whole database just for a column change?
On Tue, 2003-10-28 at 10:00, Theodore Petrosky wrote:
> why not just
>
> pg_dump dbname > olddb.out
> pico olddb.out
>
> edit the section that defines the table
> save and exit
>
> dropdb dbname
> createdb dbname
>
> psql dbname < olddb.out
>
> no fuss no muss...
>
> Ted
>
> --- mohan@physics.gmu.edu wrote:
> > Hi can we change the size of a column in postgres. I
> > have a table named
> > institution and column name is name varchar2(25), i
> > want to change it to
> > varchar2(50). Please let me know.
> >
> > --Mohan
> >
> >
> >
> >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the
> > unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> __________________________________
> Do you Yahoo!?
> Exclusive Video Premiere - Britney Spears
> http://launch.yahoo.com/promos/britneyspears/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: URGENT!!! changing Column size
@ 2003-10-28 13:42 D'Arcy J.M. Cain <darcy@druid.net>
parent: Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 1 reply; 10+ messages in thread
From: D'Arcy J.M. Cain @ 2003-10-28 13:42 UTC (permalink / raw)
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>; Theodore Petrosky <tedpet5@yahoo.com>; +Cc: mohan@physics.gmu.edu; pgsql-sql
On Tuesday 28 October 2003 08:28, Franco Bruno Borghesi wrote:
> Dopping the whole database just for a column change?
I guess some people have really small databases that don't take 3 days to dump
and reload. :-)
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: URGENT!!! changing Column size
@ 2003-10-28 15:43 Christoph Haller <ch@rodos.fzk.de>
parent: D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 0 replies; 10+ messages in thread
From: Christoph Haller @ 2003-10-28 15:43 UTC (permalink / raw)
To: darcy@druid.net; +Cc: pgsql-sql
>
> On Tuesday 28 October 2003 08:28, Franco Bruno Borghesi wrote:
> > Dopping the whole database just for a column change?
>
> I guess some people have really small databases that don't take 3 days to dump
> and reload. :-)
>
And you are on the safe side regarding indexes, views, procedures, ...
Regards, Christoph
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: URGENT!!! changing Column size
@ 2003-10-28 17:02 SZUCS Gábor <surrano@mailbox.hu>
parent: mohan@physics.gmu.edu
4 siblings, 0 replies; 10+ messages in thread
From: SZUCS Gábor @ 2003-10-28 17:02 UTC (permalink / raw)
To: pgsql-sql
I have a php script that patches database, comparing pg_catalog's tables to
input files. One thing it can do, but I can't take responsibility ;) is
changing the type of a column.
It's basically the same that everyone wrote, except that I also examine
dependencies, as broad an examination as I could think about.
However; the code is not yet ready, and not my exclusive possession, but the
steps I could tell you are:
1. Note oid:
SELECT oid, relname from pg_class WHERE relname='institution'
2. Identify the attribute:
SELECT * from pg_attribute WHERE attrelid = xxx AND ...
3. Search for depends:
SELECT d.*
FROM pg_depend d, pg_attribute a
WHERE refobjid=a.attrelid and refobjsubid=a.attnum
AND ...
-- you are on your own here ;)
4. Drop those dependencies (more likely, foreign keys, constraints, indexes)
5. Do the change
6. Apply dependencies.
HTH,
G.
------------------------------- cut here -------------------------------
----- Original Message -----
From: <mohan@physics.gmu.edu>
Sent: Monday, October 27, 2003 6:10 PM
> Hi can we change the size of a column in postgres. I have a table named
> institution and column name is name varchar2(25), i want to change it to
> varchar2(50). Please let me know.
>
> --Mohan
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2003-10-28 17:02 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-27 17:10 URGENT!!! changing Column size mohan@physics.gmu.edu
2003-10-27 17:46 ` Tomasz Myrta <jasiek@klaster.net>
2003-10-27 19:01 ` Tomasz Myrta <jasiek@klaster.net>
2003-10-27 17:48 ` CoL <col@mportal.hu>
2003-10-27 17:50 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-10-28 13:00 ` Theodore Petrosky <tedpet5@yahoo.com>
2003-10-28 13:28 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-10-28 13:42 ` D'Arcy J.M. Cain <darcy@druid.net>
2003-10-28 15:43 ` Christoph Haller <ch@rodos.fzk.de>
2003-10-28 17:02 ` SZUCS Gábor <surrano@mailbox.hu>
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