PostgreSQL: How to convert string with float number into integer?

Question

I have strange problem. Some table have integer column for which I want to insert string value with number. It works very well for strings with values such as 1, 10, etc., but fails for values with decimal dot: 3.14.

Such SQL looks like:

INSERT INTO test (intcol) VALUES ('2');
INSERT INTO test (intcol) VALUES ('1.7');

And fails with:

ERROR: invalid input syntax for integer: "1.7"

I have tried such code in Oracle and Informix. Both works but differently. Oracle rounds it and inserts 2, while Informix truncates it and inserts 1.

I know that in PostgreSQL I can use:

INSERT INTO test (intcol) VALUES (1.7);

and then PostgreSQL rounds it and inserts 2, but I want to know if I can do similar thing for strings.

As of question "why?" I can answer that this is part of bigger apliaction which uses PreparedStatement where all parameters are strings. This appliaction do not check database schema to use variables of specific type for each column, it simply uses strings, and in reality this PreparedStatement looks like:

InsertSQL("INSERT INTO " + table_name + " (" + columns + ") VALUES (" + question_marks + ")", csv_data);

But why are you trying to insert string literals into an integer column? — jarlh
– jarlh, Commented Nov 30, 2015 at 11:37
But would it not be better to perform a cast from string to number in the application instead? In my opinion, it doesn't make much sense to insert a string into a table if it's needed to be converted to a number, and let PostgreSQL do the conversion job for you. Moreover, you would have much control in your application for these strings.. — Alberto Solano
– Alberto Solano, Commented Nov 30, 2015 at 12:15

PatNowak · Accepted Answer · 2015-11-30 11:47:11Z

7

Use cast('1.7' as double precision) and then try to insert it.

INSERT INTO test (intcol) VALUES (cast('1.7' as double precision));

answered Nov 30, 2015 at 11:47

PatNowak

5,8221 gold badge27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michał Niklas Over a year ago

Thanks. I hoped that I can this without changing sql, by adding/changing database function that converts string into integer. I think that other databases I know uses NUMERIC type, so they convert sting to numeric and then numeric to integer.

Houari · Accepted Answer · 2015-11-30 12:08:05Z

2

If you realy don't have the choice to insert directly an integer, you can use round function as following:

InsertSQL("INSERT INTO " + table_name + " (" + columns + ") VALUES (round(" + question_marks + "))", csv_data);

answered Nov 30, 2015 at 12:08

Houari

5,6513 gold badges33 silver badges57 bronze badges

1 Comment

Michał Niklas Over a year ago

Thanks. See my comment to PatNowak solution. I prefer round() while cast can work as round() or simply truncate (like floor()).

Collectives™ on Stack Overflow

PostgreSQL: How to convert string with float number into integer?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related