Say I have a table called table_1 with a single column called col_1 that is of type REAL/FLOAT4 in DuckDB. How do I insert special float values like -Infinity, NaN, and Infinity into col_1? I have tried inserting those values many different ways such as "infinity", "+infinity", "inf", "+inf", etc., but with no luck. I was able to get the values to insert into a PostgreSQL table but not with DuckDB, even though they both have the same description for using and updating to these values on their webpages. For instance, here are some sample insert statements that work in my PostgreSQL table with a REAL column, but not in my DuckDB table.
INSERT INTO table_1 VALUES ('Infinity');
INSERT INTO table_1 VALUES ('-Infinity');
INSERT INTO table_1 VALUES ('NaN');
If I try these queries in my DuckDB table with a REAL/FLOAT4 column, I get an error similar to this.
Conversion Error: Could not convert string 'Infinity' to FLOAT
Below is DuckDB's description on special float values. I also posted the links to both of the databases descriptions for the numeric type, which includes a description about special float values.
"In addition to ordinary numeric values, the floating-point types have several special values:
Infinity-InfinityNaNThese represent the IEEE 754 special values “infinity”, “negative infinity”, and “not-a-number”, respectively. (On a machine whose floating-point arithmetic does not follow IEEE 754, these values will probably not work as expected.) When writing these values as constants in an SQL command, you must put quotes around them, for example: UPDATE table SET x = '-Infinity'. On input, these strings are recognized in a case-insensitive manner."
DuckDB Numeric https://duckdb.org/docs/sql/data_types/numeric
PostgreSQL Numeric https://www.postgresql.org/docs/current/datatype-numeric.html
In DuckDB we don't allow special float numbers to be stored in the columns.
It just complicates some algorithms and (I heard that) it’s not technically allowed in the SQL standard (although I've never verified that myself).