Convert postgresql column from character varying to integer

Question

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.

Dondi Michael Stroma · Accepted Answer · 2013-04-10 04:11:36Z

7

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

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

answered Apr 10, 2013 at 4:11

Dondi Michael Stroma

4,83821 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bk201 Over a year ago

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?

Dondi Michael Stroma Over a year ago

@bk201 Run UPDATE TABLE SET columnX = '0' where columnX = '' first?

Collectives™ on Stack Overflow

Convert postgresql column from character varying to integer

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related