Clash Royale CLAN TAG#URR8PPP

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

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.

change_column :vs_tickets, :ticketprice, 'integer USING CAST(ticketprice AS integer)'

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

$

4 Answers
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:

numeric

(ticketprice::numeric)::int


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

numeric

The final query is:

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


numeric

USING (ticketprice::numeric::integer)

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


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.

ERROR: syntax error at or near "Integer" LINE 1: ...ER TABLE vs_tickets ADD COLUMN ticketprice_int TYPE Integer;

try

replace(ticketprice, '.00','')::integer


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.