PostgreSQL query for Character Varying data type column with an integer value

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

          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'


After typecasting of salary in integer (see the below query)

SELECT * FROM employee where salary::INTEGER>25000


Related Posts

Fatal Error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails.

Problem: Sometimes, we get the following error when trying to make a connection in Rails.  FATAL ERROR: Peer authentication failed for us...