Can't convert postgresql table column from type varchar to int
meaning of can in hindi
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?
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)
(The parens are not strictly necessary.)
Or, as Erwin points out, just use
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);
CAN (noun) definition and synonyms, contains food, but not if it contains a drink. empty beer cans. Can definition, to be able to; have the ability, power, or skill to: She can solve the problem easily, I'm sure. See more.
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
What does CAN mean? - CAN Definition, Can may refer to: Contents. 1 Containers; 2 Music; 3 Other; 4 See also. Containers. Aluminum can · Drink can · Oil can · Steel and tin cans · Trash can Define can. can synonyms, can pronunciation, can translation, English dictionary definition of can. to be able to, have the power or skill to: I can take a bus to the
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.
Can dictionary definition, Can was the leading avant-garde rock group of the 70s. Can experimented with noise, synthesizers, non-traditional music, cut-and-paste techniques, and, most 110 synonyms of can from the Merriam-Webster Thesaurus, plus 97 related words, definitions, and antonyms. Find another word for can. Can: to bring (as an action or operation) to an immediate end.
Can, VerbEdit. can (third-person singular simple present can, present participle -, simple past could, past participle (obsolete except in Can (stylised as CAN) was a German experimental rock band formed in Cologne in 1968 by the core quartet of Holger Czukay (bass, tape editing), Irmin Schmidt (keyboards), Michael Karoli (guitar), and Jaki Liebezeit (drums).
Can, can meaning: 1. to be able to: 2. used to say that you can and will do something: 3. to be allowed to: . Learn more. A Controller Area Network (CAN bus) is a robust vehicle bus standard designed to allow microcontrollers and devices to communicate with each other's applications without a host computer. It is a message-based protocol , designed originally for multiplex electrical wiring within automobiles to save on copper, but can also be used in many other
Can, If parents can access child care, they're more likely to enter the workforce and of course, to stay employed," San Diego Mayor Kevin Faulconer Create beautiful designs with your team. Use Canva's drag-and-drop feature and layouts to design, share and print business cards, logos, presentations and more.
- Are only numbers or other chars in that field like
- The syntax seems correct. Are you sure you don't have non-numeric entries in that column?
- @JuanCarlosOropeza I added an image of the column. Would I need to use type Float instead?
- Or just use
numeric. There is no point in restricting precision or scale of the number:
- @GordonLinoff what would be the full query for Erwin's edit?
- 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?
- yep, typo on TYPE declaration. should be TYPE int (not TYPE integer) I have corrected.