Re: Unable To Change Data Type - Mailing list pgsql-general

From Andreas Kretschmer
Subject Re: Unable To Change Data Type
Date
Msg-id 20110610180100.GA5718@tux
Whole thread Raw
In response to Unable To Change Data Type  (Carlos Mennens <carlos.mennens@gmail.com>)
List pgsql-general
Carlos Mennens <carlos.mennens@gmail.com> wrote:

> For some reason I'm unable to change a column's TYPE from VARCHAR(20)
> to INTERGER or SMALLINT. I'm required to note the manufactures color
> code (value = 198) in the table data but keep getting this error and I
> don't understand why:
>
> The error I'm recieving is:
>
> ERROR:  column "color" cannot be cast to type integer
>
> The table is defined as such:
>
> pearl=# \d reference
>                                 Table "public.reference"
>  Column |         Type          |                       Modifiers
> --------+-----------------------+--------------------------------------------------------
>  id     | integer               | not null default
> nextval('reference_seq_id'::regclass)
>  type   | character varying(20) | not null
>  size   | smallint              | not null
>  color  | character varying(20) | not null
>  serial | integer               |
> Indexes:
>     "reference_pkey" PRIMARY KEY, btree (id)
>     "reference_serial_key" UNIQUE, btree (serial)
>
> The data in the database appears as such:
>
> pearl=# SELECT id, color FROM reference ORDER BY id;
>  id | color
> ----+-------
>   1 | 198
>   2 | 198
>   3 | 198
>   4 | 198
>   5 | 198
>   6 | 198
> (6 rows)
>
> Is this not possible to change the data type from VARCHAR to INTERGER
> or something numeric since only manufacturer color codes will be
> stored?

It's possible, but you have to use the correct syntax. See my example:

test=*# select * from foo;
 t
---
 1
 2
 3
 4
(4 rows)

Time: 0,929 ms
test=*# alter table foo alter column t type int using (t::int);
ALTER TABLE
Time: 50,810 ms
test=*# \d foo;
      Table "public.foo"
 Column |  Type   | Modifiers
--------+---------+-----------
 t      | integer |

test=*# select * from foo;
 t
---
 1
 2
 3
 4
(4 rows)



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

pgsql-general by date:

Previous
From: Bill Moran
Date:
Subject: Re: Unable To Change Data Type
Next
From: Carlos Mennens
Date:
Subject: Re: Unable To Change Data Type