Serialize None to null for numeric fields ?

[Standaa]

This is a question rather than an issue. I saw in the docs that:

PostgreSQL numeric does not support Decimal('Inf') or Decimal('-Inf'). pgcopy serializes these as NaN.

However I can usually set null values in postgres using Js by passing null or empty values. Is there any way to replace NaNs with null in python pgcopy ?

I have tried : Decimal(row["my_field"]) if 'my_field' in row else None, and : Decimal(row["my_field"]) if 'my_field' in row else 'NULL',

Both end up being interpreted as NaNs.

[altaurog]

I can’t diagnose your problem without more context, and I can’t reproduce the behavior you report. Here’s a test with the following table:

weather_db=> CREATE TABLE numtest (id serial, name text, val numeric);

Running the following python code:

from decimal import Decimal
from pgcopy import CopyManager
import psycopg2

cols = ('name', 'val')
records = [
    ('one', Decimal('1.0')),
    ('oneone', Decimal('1.1')),
    ('null', None),
    ('naught', Decimal('0.0')),
    ('inf', Decimal('Inf')),
]

conn = psycopg2.connect(database='weather_db')
mgr = CopyManager(conn, 'numtest', cols)
mgr.copy(records)

conn.commit()

Inserts these rows to the db:

weather_db=> SELECT *, val IS NULL as isnull FROM numtest;
 id |  name  | val | isnull 
----+--------+-----+--------
  1 | one    | 1.0 | f
  2 | oneone | 1.1 | f
  3 | null   |     | t
  4 | naught | 0.0 | f
  5 | inf    | NaN | f
(5 rows)

Is that not the desired behavior?