Error "invalid input syntax for xxx" Is Reported During Data Type Conversion

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: invalid input syntax for integer 5.0

Possible Causes

During SQL execution, if an error similar to "invalid input syntax for integer/bigint/numeric" is reported. It is most likely there is a data type conversion error. For example, the character a or space is converted to the integer or bigint type.

If you are familiar with the numeric and character types of GaussDB(DWS), you can avoid data type usage problems. For details, see section "Data Types" in SQL Syntax ReferenceData Types.

Handling Procedure

For example, if an error is reported when the string type varchar is directly converted to the integer type, you can change the column type to decimal (any precision) and then perform type conversion.

Here is the procedure:

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

   1	SELECT * FROM PG_GET_TABLEDEF('product');

   

2. Convert the query result to an integer.

   1	SELECT CAST(price AS integer) FROM product;

   The following error information is displayed:

   

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

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

4. Data is successfully converted to an integer.

   1	SELECT CAST(price AS integer) FROM product;