Re: 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.) :-(.

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(dot)l(dot)johnson(at)cox(dot)net
Jefferson, LA USA

PETA - People Eating Tasty Animals

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
> > 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;

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).

--- Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)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

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(dot)l(dot)johnson(at)cox(dot)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