pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: How to increase Column width of table
5+ messages / 4 participants
[nested] [flat]
* Re: How to increase Column width of table
@ 2003-03-25 15:10 Atul Pedgaonkar <atul@imcindia.net>
0 siblings, 0 replies; 5+ messages in thread
From: Atul Pedgaonkar @ 2003-03-25 15:10 UTC (permalink / raw)
To: pgsql-sql
Hello,
Atul here, i have one table and i would like to increase the length of
existing column and the sql statement is
Exisiting Column is "vehicle_make" varchar(30)
SQL: alter table commute_profile alter column "vehicle_make"
varchar(100)
But this gives error.
ERROR: parser: parse error at or near "varchar"
Please anyone give me the solution for the above.
Thank you.
Regards,
Atul
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to increase Column width of table
@ 2003-03-25 15:18 Christoph Haller <ch@rodos.fzk.de>
2003-03-25 16:46 ` Re: How to increase Column width of table Robert Treat <xzilla@users.sourceforge.net>
2003-03-25 16:50 ` Re: How to increase Column width of table Andreas Pflug <Andreas.Pflug@web.de>
0 siblings, 2 replies; 5+ messages in thread
From: Christoph Haller @ 2003-03-25 15:18 UTC (permalink / raw)
To: pgsql-sql; +Cc: atul@imcindia.net
>
> Atul here, i have one table and i would like to increase the length
of
> existing column and the sql statement is
>
> Exisiting Column is "vehicle_make" varchar(30)
>
> SQL: alter table commute_profile alter column "vehicle_make"
> varchar(100)
>
> But this gives error.
>
> ERROR: parser: parse error at or near "varchar"
>
I'm afraid there is no one-step-solution to this.
You'll have to go the hard way as
CREATE new_table (
...
... varchar(100),
...
);
INSERT INTO new_table as SELECT * FROM old_table;
DROP old_table;
ALTER TABLE new_table RENAME TO old_table;
And don't forget about re-creating related indexes etc.
Regards, Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to increase Column width of table
2003-03-25 15:18 Re: How to increase Column width of table Christoph Haller <ch@rodos.fzk.de>
@ 2003-03-25 16:46 ` Robert Treat <xzilla@users.sourceforge.net>
1 sibling, 0 replies; 5+ messages in thread
From: Robert Treat @ 2003-03-25 16:46 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql; atul@imcindia.net
http://fts.postgresql.org/db/mw/msg.html?mid=1071582
On Tue, 2003-03-25 at 10:18, Christoph Haller wrote:
> >
> > Atul here, i have one table and i would like to increase the length
> of
> > existing column and the sql statement is
> >
> > Exisiting Column is "vehicle_make" varchar(30)
> >
> > SQL: alter table commute_profile alter column "vehicle_make"
> > varchar(100)
> >
> > But this gives error.
> >
> > ERROR: parser: parse error at or near "varchar"
> >
> I'm afraid there is no one-step-solution to this.
> You'll have to go the hard way as
>
> CREATE new_table (
> ...
> ... varchar(100),
> ...
> );
> INSERT INTO new_table as SELECT * FROM old_table;
> DROP old_table;
> ALTER TABLE new_table RENAME TO old_table;
> And don't forget about re-creating related indexes etc.
>
> Regards, Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to increase Column width of table
2003-03-25 15:18 Re: How to increase Column width of table Christoph Haller <ch@rodos.fzk.de>
@ 2003-03-25 16:50 ` Andreas Pflug <Andreas.Pflug@web.de>
1 sibling, 0 replies; 5+ messages in thread
From: Andreas Pflug @ 2003-03-25 16:50 UTC (permalink / raw)
To: pgsql-sql
Christoph Haller wrote:
>I'm afraid there is no one-step-solution to this.
>You'll have to go the hard way as
>
>CREATE new_table (
>...
>... varchar(100),
>...
>);
>INSERT INTO new_table as SELECT * FROM old_table;
>DROP old_table;
>ALTER TABLE new_table RENAME TO old_table;
>And don't forget about re-creating related indexes etc.
>
>Regards, Christoph
>
>
This has many side effects, like dropped indices that have to be
recreated etc.
You can rename your old column, create a new column varchar(100), update
... set newCol=oldCol, drop oldCol and rename newCol to oldCol
See ALTER TABLE doc for this.
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to increase Column width of table
@ 2003-03-26 08:08 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 5+ messages in thread
From: Christoph Haller @ 2003-03-26 08:08 UTC (permalink / raw)
To: pgsql-sql; +Cc: atul@imcindia.net; xzilla@users.sourceforge.net; Andreas.Pflug@web.de
>
> http://fts.postgresql.org/db/mw/msg.html?mid=1071582
>
resp.
>
> You can rename your old column, create a new column varchar(100),
update
> ... set newCol=oldCol, drop oldCol and rename newCol to oldCol
> See ALTER TABLE doc for this.
>
Ok, this is the accurate solution to minimize side effects.
Thanks to Robert and Andreas for pointing this out.
Regards, Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-03-26 08:08 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-25 15:10 Re: How to increase Column width of table Atul Pedgaonkar <atul@imcindia.net>
2003-03-25 15:18 Re: How to increase Column width of table Christoph Haller <ch@rodos.fzk.de>
2003-03-25 16:46 ` Robert Treat <xzilla@users.sourceforge.net>
2003-03-25 16:50 ` Andreas Pflug <Andreas.Pflug@web.de>
2003-03-26 08:08 Re: How to increase Column width of table Christoph Haller <ch@rodos.fzk.de>
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