It is difficult
to change the column data-type character varying to integer in running
database, but need to query the string. Suppose we have a table which column
Cal_1 has integer value as string, in PostgreSQL statement like Col_1>100
not return the appropriate data.
Using typecast you can resolve this (Character Varying data type column with integer value) issue.
WHERE
Col_1::INTEGER > int value
For the example
we have the following table
CREATE TABLE
employee
(
ID Serial NOT NULL,
first_name CHARACTER VARYING(100),
last_name CHARACTER VARYING(100),
salary CHARACTER VARYING(10)
);
--insert some records
INSERT INTO
employee (first_name, last_name, salary)VALUES('Dilip','Singh','25000');
INSERT INTO
employee (first_name, last_name, salary)VALUES('Asish','Pratap','20000');
INSERT INTO
employee (first_name, last_name, salary)VALUES('Naina','Singh','200000');
INSERT INTO
employee (first_name, last_name, salary)VALUES('Mamta','Singh','10000');
|
If you will run the following query it will not
return the appropriate data
SELECT *
FROM employee
where salary>'25000'
|
Result:
After typecasting of salary in integer (see the
below query)
SELECT *
FROM employee
where salary::INTEGER>25000
|
Result: