Help Center> Data Warehouse Service> Troubleshooting> Database Use> "invalid input syntax for integer" Is Reported
View PDF

"invalid input syntax for integer" Is Reported

Symptom

The type of a table column is varchar(20), and the data is 5.0. When cast(xxx as integer) is used to convert the data to an integer, an error is reported. The error information is as follows:

Possible Causes

The varchar data type cannot be directly converted to the integer type. You can change the column type to decimal (any precision), then perform conversion.

Handling Procedure

  1. Reproduce the fault. Assuming that the name of the error table is product, define the table as follows: 

    1
    		 
    SELECT * FROM PG_GET_TABLEDEF('product');

  2. Run the following SQL statement and convert the query result to an integer. The error is reported as follows: 

    1
    		 
    SELECT CAST(price AS integer) FROM product;

  3. Change the data type of the column to decimal. 

    1
    		 
    ALTER TABLE product ALTER COLUMN price TYPE decimal(10,1);

  4. Retry the query again. The integer conversion is successful. 

    1
    		 
    SELECT CAST(price AS integer) FROM product;

Parent topic: Database Use