Can't convert postgresql table column from type varchar to int

Question

I have a database table of that I have used to store the data returned from a web spider. I have a column that contains ticket prices for different events all in the varchar type (as the scrapy spider has to scrape the data in unicode). I'm trying to return the min price of the column and since the min() function only works for data of type INT, I tried to convert the column to integers using a solution from this SO post:

ALTER TABLE vs_tickets ALTER COLUMN ticketprice TYPE integer USING (ticketprice::integer);

but I got the error: ERROR: invalid input syntax for integer:

I also tried: change_column :vs_tickets, :ticketprice, 'integer USING CAST(ticketprice AS integer)' but that didn't work either.

What is the proper way to convert the column to type INT?

Edit:

The syntax seems correct. Are you sure you don't have non-numeric entries in that column? — Mureinik, Aug 28 '15 at 22:11
@JuanCarlosOropeza I added an image of the column. Would I need to use type Float instead? — loremIpsum1771, Aug 28 '15 at 22:29

Gordon Linoff · Accepted Answer · 2015-08-29T02:19:46.677

4

You have decimal places in the string, so a simple cast is not going to work. You can do a double conversion:

cast(cast(ticketprice as decimal(10, 2)) as int)

or:

(ticketprice::decimal(10, 2))::int

(The parens are not strictly necessary.)

EDIT:

Or, as Erwin points out, just use numeric:

(ticketprice::numeric)::int

Postgres is much smarter about numeric than most other databases . . . after all, it supports numbers that are egregiously large ;)

The final query is:

ALTER TABLE vs_tickets
    ALTER COLUMN ticketprice TYPE integer USING (ticketprice::numeric::integer);

edited Aug 29 '15 at 02:19

answered Aug 28 '15 at 22:45

Gordon Linoff

1,242,037
58
646
786

Or just use `numeric`. There is no point in restricting precision or scale of the number: `USING (ticketprice::numeric::integer)` – Erwin Brandstetter Aug 28 '15 at 23:40
@GordonLinoff what would be the full query for Erwin's edit? – loremIpsum1771 Aug 29 '15 at 02:14

score 2 · Answer 2 · edited May 23 '17 at 10:26

I'm going to bet on your column have wrong characters. Also you may want use float or numeric because you will lose decimals if convert to integers.

You need create a function to check if a text is numeric like this isnumeric-with-postgresql

Then check each row like this

select ticketprice
from vs_tickets 
where ISNUMERIC(ticketprice)  = false;

As your comment you also should try

SELECT ticketprice::float

Patrick · Answer 3 · 2015-09-01T21:49:49.170

1

You will be best off adding an INT column, moving your data with a cast and then removing the old varchar column.

ALTER TABLE vs_tickets ADD COLUMN ticketprice_int TYPE int;
GO
update vs_tickets SET ticketprice_int = cast(ticketprice as int);
// if you fail to cast the varchar to int you can use Gordon's method
// update vs_tickets SET ticketprice_int = cast(cast(ticketprice as decimal(10, 2)) as int);
GO
ALTER TABLE vs_tickets DROP COLUMN ticketprice;
GO
ALTER TABLE vs_tickets RENAME COLUMN ticketprice_int to ticketprice;
GO

With this at minimum you will be able to tell if and where a cast/convert fails and be able to check and recheck at each step before you can't turn back.

edited Sep 01 '15 at 21:49

answered Aug 28 '15 at 23:27

Patrick

416
3
7

I'm getting the message: `ERROR: syntax error at or near "Integer" LINE 1: ...ER TABLE vs_tickets ADD COLUMN ticketprice_int TYPE Integer;` for the first line in the query. Is it because of an incorrect type or something else? – loremIpsum1771 Aug 29 '15 at 02:19
yep, typo on TYPE declaration. should be TYPE int (not TYPE integer) I have corrected. – Patrick Sep 01 '15 at 21:49

Can't convert postgresql table column from type varchar to int

Edit:

3 Answers3