pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Reg: Changing Column type
4+ messages / 3 participants
[nested] [flat]

* Reg: Changing Column type
@ 2005-11-15 11:52 sandhya <sandhyar@amiindia.co.in>
  2005-11-15 14:32 ` Re: Reg: Changing Column type Tom Lane <tgl@sss.pgh.pa.us>
  2005-11-16 19:35 ` Re: Reg: Changing Column type Bruno Wolff III <bruno@wolff.to>
  0 siblings, 2 replies; 4+ messages in thread

From: sandhya @ 2005-11-15 11:52 UTC (permalink / raw)
  To: pgsql-admin; +Cc: sandhya <sandhyar@amiindia.co.in>

Hi,

When i am tring to change the column type from character/Varchar to "int" i am getting error like,

ERROR:  column "fname" cannot be cast to type "int4"

I can able to change the column type to "int " if the old datatype is double etc.
Even a "int" datatype also i can able to conver to "char"
and why not viceversa....Please tell me where the problem is.
Suggest me what to do to convert a "char" datatype to "int".

Thanks&Regards,
Sandhya

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Reg: Changing Column type
  2005-11-15 11:52 Reg: Changing Column type sandhya <sandhyar@amiindia.co.in>
@ 2005-11-15 14:32 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 4+ messages in thread

From: Tom Lane @ 2005-11-15 14:32 UTC (permalink / raw)
  To: sandhya <sandhyar@amiindia.co.in>; +Cc: pgsql-admin

"sandhya" <sandhyar@amiindia.co.in> writes:
> When i am tring to change the column type from character/Varchar to =
> "int" i am getting error like,
> ERROR:  column "fname" cannot be cast to type "int4"

If this is a pre-8.0 PG version, try casting to text and then int4.

If 8.0 or later, it should work as is --- would you show us exactly
what you did?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Reg: Changing Column type
  2005-11-15 11:52 Reg: Changing Column type sandhya <sandhyar@amiindia.co.in>
@ 2005-11-16 19:35 ` Bruno Wolff III <bruno@wolff.to>
  1 sibling, 0 replies; 4+ messages in thread

From: Bruno Wolff III @ 2005-11-16 19:35 UTC (permalink / raw)
  To: sandhya <sandhyar@amiindia.co.in>; +Cc: pgsql-admin

On Tue, Nov 15, 2005 at 17:22:16 +0530,
  sandhya <sandhyar@amiindia.co.in> wrote:
> Hi,
> 
> When i am tring to change the column type from character/Varchar to "int" i am getting error like,
> 
> ERROR:  column "fname" cannot be cast to type "int4"
> 
> I can able to change the column type to "int " if the old datatype is double etc.
> Even a "int" datatype also i can able to conver to "char"
> and why not viceversa....Please tell me where the problem is.
> Suggest me what to do to convert a "char" datatype to "int".

One way to do this is to add a new int column. Use a query to insert data into
the new column using to_number allow with whatever you need to handle strings
that aren't numbers.
Then delete the old column.
Then rename the new column to be the original name.

In at least 8.1 there is a USING clause which allows you to specify a
conversion function which lets you do the above in one step.
I don't know when that feature was added though.



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Fw: Reg: Changing Column type
@ 2005-11-16 13:07 sandhya <sandhyar@amiindia.co.in>
  0 siblings, 0 replies; 4+ messages in thread

From: sandhya @ 2005-11-16 13:07 UTC (permalink / raw)
  To: pgsql-admin; +Cc: sandhya <sandhyar@amiindia.co.in>


> Hi all,
> I have PostGreSql -8 .2.0 version only.
>
> say i have a table(sample) with 2 columns like:
> name varchar(20),age int;
>
> But when i give ALTER TABLE sample ALTER COLUMN age TYPE char;
> It is working fine.
>
> BUT  when i try to do the same for converting the "char/text" type data to
> "int" still i am getting error as,
>
> like,ALTER TABLE sample ALTER COLUMN age TYPE int;
>
> ERROR: column "age" cannot be cast to type "pg_catalog.int4"
>
> Pls tell me how to do this.Am i wrong some where.If So,suggest me.
>
> Thanks&Regards
> Sandhya R
>
>
>
>
>
> ----- Original Message ----- 
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
> To: "sandhya" <sandhyar@amiindia.co.in>
> Cc: "postgre" <pgsql-admin@postgresql.org>
> Sent: Tuesday, November 15, 2005 8:02 PM
> Subject: Re: [ADMIN] Reg: Changing Column type
>
>
> > "sandhya" <sandhyar@amiindia.co.in> writes:
> > > When i am tring to change the column type from character/Varchar to =
> > > "int" i am getting error like,
> > > ERROR:  column "fname" cannot be cast to type "int4"
> >
> > If this is a pre-8.0 PG version, try casting to text and then int4.
> >
> > If 8.0 or later, it should work as is --- would you show us exactly
> > what you did?
> >
> > regards, tom lane
>




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-11-16 19:35 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-15 11:52 Reg: Changing Column type sandhya <sandhyar@amiindia.co.in>
2005-11-15 14:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-16 19:35 ` Bruno Wolff III <bruno@wolff.to>
2005-11-16 13:07 Fw: Reg: Changing Column type sandhya <sandhyar@amiindia.co.in>

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