Thread: Modyifying a column's datatype
Greetings –
It looks to me as if there is no way to alter the datatype of a table column, as in from timestamp to varchar. Am I right? (I will cry if I’m right.) L.
It appears that I must drop and then re-create the table structure, with my new column datatype, in order to keep the columns in the current order – true?
Thanks for your input.
Louise
On Wed, 2003-09-03 at 14:56, Louise Cofield wrote: > Greetings – > > > > It looks to me as if there is no way to alter the datatype of a table > column, as in from timestamp to varchar. Am I right? (I will cry if > I’m right.) L. > > It appears that I must drop and then re-create the table structure, > with my new column datatype, in order to keep the columns in the > current order – true? There are references to this all throughout the archives. begin; alter table foo add column bar varchar; update foo set bar = cast(sniffle as varchar); alter table foo drop column sniffle; alter table foo rename column bar to sniffle; commit; -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA USA PETA - People Eating Tasty Animals
--- Ron Johnson <ron.l.johnson@cox.net> wrote: > On Wed, 2003-09-03 at 14:56, Louise Cofield wrote: > > Greetings � > > > > > > > > It looks to me as if there is no way to alter the > datatype of a table > > column, as in from timestamp to varchar. Am I > right? (I will cry if > > I�m right.) L. > > > > It appears that I must drop and then re-create the > table structure, > > with my new column datatype, in order to keep the > columns in the > > current order � true? > > There are references to this all throughout the > archives. > > begin; > alter table foo add column bar varchar; > update foo set bar = cast(sniffle as varchar); > alter table foo drop column sniffle; > alter table foo rename column bar to sniffle; > commit; I don't believe that this will preserve the column order, which the OP wanted. The only alternative that I know of is to hack the system tables, but that is definitely risky. Column order is not supposed to matter, but it is amazing how many (brain dead) applications there are that rely on it. __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com
On Wed, 3 Sep 2003, Ron Johnson wrote: > On Wed, 2003-09-03 at 14:56, Louise Cofield wrote: > > Greetings � > > > > It looks to me as if there is no way to alter the datatype of a table > > column, as in from timestamp to varchar. Am I right? (I will cry if > > I�m right.) L. > > > > It appears that I must drop and then re-create the table structure, > > with my new column datatype, in order to keep the columns in the > > current order � true? > > There are references to this all throughout the archives. > > begin; > alter table foo add column bar varchar; > update foo set bar = cast(sniffle as varchar); > alter table foo drop column sniffle; > alter table foo rename column bar to sniffle; > commit; Just to be clear, however, that won't keep the columns in the same order (although in general, that shouldn't be relied on anyway).
Hi, If your data is good for the new type as it is, one way to alter name and type is by editing the dumb -c file's CREATE TABLE statement. This will also preserve your column order, if it really matters. BR, Aarni On Thursday 04 September 2003 01:55, you wrote: > --- Ron Johnson <ron.l.johnson@cox.net> wrote: > > On Wed, 2003-09-03 at 14:56, Louise Cofield wrote: > > > Greetings > > > > > > > > > > > > It looks to me as if there is no way to alter the > > > > datatype of a table > > > > > column, as in from timestamp to varchar. Am I > > > > right? (I will cry if > > > > > Im right.) L. > > > > > > It appears that I must drop and then re-create the > > > > table structure, > > > > > with my new column datatype, in order to keep the > > > > columns in the > > > > > current order true? > > > > There are references to this all throughout the > > archives. > > > > begin; > > alter table foo add column bar varchar; > > update foo set bar = cast(sniffle as varchar); > > alter table foo drop column sniffle; > > alter table foo rename column bar to sniffle; > > commit; > > I don't believe that this will preserve the column > order, which the OP wanted. The only alternative that > I know of is to hack the system tables, but that is > definitely risky. > > Column order is not supposed to matter, but it is > amazing how many (brain dead) applications there are > that rely on it. > > __________________________________ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html