Psycopg3: NaN to null conversion in COPY statements

Question

In psycopg2 it was possible to covert NaN values to nulls, using adapters like this:

__REGISTERED = False


def _nan_to_null(f,
        _NULL=psycopg2.extensions.AsIs('NULL'),
        _Float=psycopg2.extensions.Float):
    if not np.isnan(f):
        return _Float(f)
    return _NULL


def register_nan_adapter():
    global __REGISTERED
    if not __REGISTERED:
        print('Register nan to null adapter for psycopg2...')
        psycopg2.extensions.register_adapter(float, _nan_to_null)
        __REGISTERED = True
    else:
        print('nan to null adapter for psycopg2 is already registered!')

How can the same goal be achieved in psycopg3?

The docs say you can create a Dumper for each datatype, so I did this at first:

class NullNan(FloatDumper):
    def dump(self, elem):
        if np.isnan(elem):
            return b"NULL"
        else:
            return super().dump(elem)


connection.adapters.register_dumper(float, NullNan)

But this does not work. Returning just None does not help neither.

Update after Adrian Clavier answer:

It seems that having the dumper to return None is fine for inserts and updates, but will not work with a COPY:

class NullNan(FloatDumper):
    def dump(self, elem):
        if np.isnan(elem):
            return None
        else:
            return super().dump(elem)

with cursor.copy(f"COPY _r ({col_names_str}) FROM STDIN") as copy:
                for index, row in df.iterrows():
                    rec = row.values.tolist()
                    copy.write_row(rec)

Leads to exception:

psycopg.errors.QueryCanceled: COPY from stdin failed: error from Python: TypeError - expected string or bytes-like object

This, "what is the new way ..." is not clear. 1) Are you asking how to do this in `psycopg(3)`? 2) Have you tried it and if so what was the failure? 3) Where is `np.isnan()` coming from? I can guess, but it best to be explicit. **Add information as update to question**. — Adrian Klaver, Oct 23 '22 at 16:42
Assuming you are talking about `psycopg(3)` you should look at [Data adaptation](https://www.psycopg.org/psycopg3/docs/advanced/adapt.html) in particular the *Example: handling infinity date* section as a guideline. — Adrian Klaver, Oct 23 '22 at 16:53
I updated the text. Data adaptation link code does not work, because I cannot return None from the Dumper. — gosuer1921, Oct 23 '22 at 17:21
What version of `psycopg`? As it works for me in the answer below using `psycopg==3.1.3`. — Adrian Klaver, Oct 23 '22 at 17:24
Yeah I am seeing same thing. I suspect that this will need some combination of binary copy and set_types. I will have to get back to this later. — Adrian Klaver, Oct 23 '22 at 17:54
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. — Community, Oct 23 '22 at 17:56
See UPDATE to answer. It is not complete as it yields `NaN` not `NULL`, still I think it is a step in the right direction. — Adrian Klaver, Oct 23 '22 at 21:19
Does it work for binary dumpers only? Don't see any difference except this against NullNan in my example. — gosuer1921, Oct 24 '22 at 13:41
I am not following, you will need to provide a more complete explanation of what you are seeing or not seeing on your end. — Adrian Klaver, Oct 24 '22 at 15:37

Adrian Klaver · Answer 1 · 2022-10-24T00:12:51.977

Hmm, that was easier then I thought. Actually not as the two updates attest.

import numpy as np
import psycopg
from psycopg.types.numeric import FloatDumper

class NanDumper(FloatDumper):
    def dump(self, obj):
        if np.isnan(obj):
            return None
        else:
            return super().dump(obj)

con = psycopg.connect(dbname="test", host='localhost', user='postgres', port=5432)
cur = con.cursor()
cur.adapters.register_dumper(float, NanDumper)

cur.execute("select %s::float", [np.NaN]).fetchone()                                                                                                                      
(None,)


cur.execute("select %s::float", [3.68]).fetchone()                                                                                                                        
(3.68,)

\d float_test 
                   Table "public.float_test"
  Column   |       Type       | Collation | Nullable | Default 
-----------+------------------+-----------+----------+---------
 id        | integer          |           |          | 
 float_fld | double precision |           |          | 

cur.execute("insert into float_test(id, float_fld) values(%s,  %s)", [1, np.NaN])
cur.execute("insert into float_test(id, float_fld) values(%s,  %s)", [2, 4.56])
con.commit()

select * from float_test;
 id | float_fld 
----+-----------
  1 |      NULL
  2 |      4.56

UPDATE using COPY. Partial solution.

data = [(5, 8.92), (6, np.NaN)]
with cur.copy("COPY float_test (id, float_fld) FROM STDIN  WITH (FORMAT BINARY)") as copy:
    copy.set_types(["int4", "float8"])
    for row in data:
        copy.write_row(row)
con.commit()

select * from float_test;
 id | float_fld 
----+-----------
  5 |      8.92
  6 |       NaN

NaN is a valid 'number' per Numeric Types. Not sure why the NanDumper conversion is not being picked up.

UPDATE #2. Found the magic sauce:

from psycopg.types.numeric import FloatBinaryDumper

class NanBinaryDumper(FloatBinaryDumper):
    def dump(self, obj):
        if np.isnan(obj):
            return None
        else:
            return super().dump(obj)
con = psycopg.connect(dbname="test", host='localhost', user='postgres', port=5432)
cur = con.cursor()
cur.adapters.register_dumper(float, NanBinaryDumper)

with cur.copy("COPY float_test (id, float_fld) FROM STDIN WITH (FORMAT BINARY)") as copy:
    copy.set_types(["int4", "float8"])
    for row in data:
        copy.write_row(row)
con.commit()

select * from float_test;
 id | float_fld 
----+-----------
  5 |      8.92
  6 |      NULL

It seems that it works for usual insert, but fails with COPY: `with cursor.copy(f"COPY _r ({col_names_str}) FROM STDIN") as copy: for index, row in df.iterrows(): rec = row.values.tolist() copy.write_row(rec)` — gosuer1921, Oct 23 '22 at 17:26
1) Add that code to question. 2) Do not use `f` strings. The [Copy](https://www.psycopg.org/psycopg3/docs/basic/copy.html#copy) section shows how to use the `psycopg` [sql](https://www.psycopg.org/psycopg3/docs/api/sql.html#module-psycopg.sql) to do this properly. 3) To your question add the error message you get. — Adrian Klaver, Oct 23 '22 at 17:31

Psycopg3: NaN to null conversion in COPY statements

1 Answers1