Passing NaN floats to QuestDB

Question

I get the following error when trying to pass a NaN float type into QuestDB

psycopg2.DatabaseError: unsupported type 'NaN'::float

In QuestDB documentation here https://questdb.io/docs/reference/sql/datatypes/ it says i can pass a NaN but not sure how i convert the python value and interpolate it into an sql query?

Python code is something like this

query = "INSERT INTO database_name ({}) VALUES "%s, %s, %s"".format(
      columns)
    )
cursor.executemany(query, values)

Answer 1

AFAIK QuestDB's :: function doesn't support casting values to float.
But when psycopg2 meets a NaN, it will fill the prepared statement as 'NaN'::float, which cannot be parsed by QDB.

My test case (doesn't work):

query = 'insert into float_table values (%s)'
vals = [[1.0], [np.NaN], [float('nan')]]
cursor.executemany(query, vals)

These modifications make it works, I know it's not elegant :(

query = 'insert into float_table values (cast(%s as float))'  # 1. let QDB cast every thing, float itself, or string 'NaN'
vals = [[1.0], [np.NaN], [float('nan')]]
vals_ng = [['NaN'] if math.isnan(v[0]) else v for v in vals]  # 2. change the NaNs to string
cursor.executemany(query, vals_ng)

Update: this bug has been fixed, and you can direct use the original code to insert NaN/Infinity since 6.3.1