3

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.

bk201
  • 346
  • 2
  • 5
  • 14

1 Answers1

7

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

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

Dondi Michael Stroma
  • 4,668
  • 18
  • 21
  • Now i get back: ERROR: invalid input syntax for type numeric: "" So i'm guessing it works for the rows that have data in them, but when it see's a row with an empty string, i get that error? – bk201 Apr 10 '13 at 05:12
  • @bk201 Run `UPDATE TABLE SET columnX = '0' where columnX = ''` first? – Dondi Michael Stroma Apr 10 '13 at 05:23