Unable To Change Data Type

In response to Carlos Mennens <carlos.mennens@gmail.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/

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�

On Fri, Jun 10, 2011 at 1:57 PM, Bill Moran <wmoran@potentialtech.com> wrote:

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

Your command suggestion worked perfect but can you explain why yours
worked and mine didn't? I've never used 'USING CAST' command before.

In response to Carlos Mennens <carlos.mennens@gmail.com>:

On Fri, Jun 10, 2011 at 1:57 PM, Bill Moran <wmoran@potentialtech.com> wrote:

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

Your command suggestion worked perfect but can you explain why yours
worked and mine didn't? I've never used 'USING CAST' command before.

Apparently, PostgreSQL isn't sure of how to do the conversion, so you
have to tell it. You could just as easily given any valid expression
to the USING clause -- if your conversion was more complex than simply
a cast.

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

On 06/10/11 11:02 AM, Carlos Mennens wrote:

On Fri, Jun 10, 2011 at 1:57 PM, Bill Moran<wmoran@potentialtech.com> wrote:

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

Your command suggestion worked perfect but can you explain why yours
worked and mine didn't? I've never used 'USING CAST' command before.

pgsql <= 8.3 was lax about char->other casting. due to some discovered
ambiguities, it was tightened up in 8.4+

--
john r pierce N 37, W 122
santa cruz ca mid-left coast