Re: Unable To Change Data Type

In response to Carlos Mennens <carlos(dot)mennens(at)gmail(dot)com>:

> 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

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

I don't think ALTER COLUMN TYPE will implicitly convert from varchar
to INT.

Try:
ALTER TABLE reference
ALTER COLUMN color
TYPE INT
USING CAST(color AS INT);

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/