Postgres Pro
Downloads
Home   >   mailing lists

Thread: Unable To Change Data Type

Unable To Change Data Type

From
Carlos Mennens
Date:
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?

Re: Unable To Change Data Type

From
Bill Moran
Date:
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/

Re: Unable To Change Data Type

From
Andreas Kretschmer
Date:
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°

Re: Unable To Change Data Type

From
Carlos Mennens
Date:
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.

Re: Unable To Change Data Type

From
Bill Moran
Date:
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/

Re: Unable To Change Data Type

From
John R Pierce
Date:
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