[SOLVED] Convert postgresql column from character varying to integer

Convert postgresql column from character varying to integer

I'm trying to change a column type from "character varying(15)" to an integer.

If I run "=#SELECT columnX from tableY limit(10);" I get back:

columnX 
----------
34.00
12.00
7.75
18.50

4.00
11.25

18.00
16.50

If i run "=#\d+ columnX" i get back:

     Column     |         Type          |                           Modifiers                           | Storage  | Description 

columnX       | character varying(15) | not null                                                      | extended |

I've searched high and low, asked on the postgresql irc channel, but no one could figure out how to change it, I've tried:

ALTER TABLE race_horserecord ALTER COLUMN win_odds TYPE integer USING (win_odds::integer);

Also:

ALTER TABLE tableY ALTER COLUMN columnX TYPE integer USING (trim("columnX")::integer);

Every time I get back:

"ERROR: invalid input syntax for integer: "34.00""

Any help would be appreciated.

Solution

Try USING (win_odds::numeric::integer).

Note that it will round your fractional values (e.g., '7.75'::numeric::integer = 8).