Thread: Modyifying a column's datatype

Modyifying a column's datatype

From
"Louise Cofield"
Date:

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

 

 

Re: Modyifying a column's datatype

From
Ron Johnson
Date:
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


Re: Modyifying a column's datatype

From
Jeff Eckermann
Date:
--- 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

Re: Modyifying a column's datatype

From
Stephan Szabo
Date:
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).


Re: Modyifying a column's datatype

From
Aarni Ruuhimäki
Date:
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
> >
> > > 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html