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);

Answer 1

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

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