json adapter None conversion

[jerch]

The psycopg json default adapter always calls the json.dumps converter for any object input, thus would convert None into a json string of 'null'. In postgres the difference boils down to 'null'::jsonb (json null) vs null::jsonb (sql NULL). This also means, that a json column can never be null in SQL, if fed from the default adapter.

I wonder if sql NULL is the better default way to handle None, as it would be more in line semantically with None in other column types. Is there a specific reason, I am not aware of, to encode it in json?

[dvarrazzo]

Json is a wrapper, not an adapter, and it suggests the adaptation mechanism "this object is to be dumped as json". it sounds like you are adapting Json(None), which would be probably transformed into a 'null'::json. If you dump a None it will be dumped as NULL.

If you want a None to be transformed into NULL, one possibility is to keep it out of the Json wrapper at application time, e.g.

cursor.execute("INSERT INTO jsondata VALUES (%s)", [Json(obj) if obj is not None else None])`). 

You can also customize the json dump method yourself as described in the docs. For instance (untested):

class MyJson(Json):
    def dumps(self, obj):
        return super().dumps(obj) if obj is not None else "NULL"

curs.execute("insert into mytable (jsondata) values (%s)",
    [MyJson({'a': 100})])

This is probably valid in the context of your application, but not suitable as a default in psycopg2, and wouldn't work in psycopg 3 (as explained below). As psycopg is a driver, its aim is to provide a complete mechanism to cover the entire data domain. Different applications may have different needs and I don't think that everyone would be happy with a transformation Json(None) -> NULL because:

• it might not be what they want (a policy choice, not mechanism)
• it would make impossible to represent 'null'::json in Python (not complete)
• it breaks the model of NULL being a value outside the domain of the data type (not orthogonal)

The latter also makes hard and inefficient to use server-side binding with PQexecParams() and similar (used in psycopg 3) because the path of inserting NULL is different from the path of inserting non-null data: we should ask every dumper object "do you want to become null?" instead of just dump None -> NULL and every non-None object as non-NULL. It might become possible in future psycopg 3 releases if we introduce a client-binding cursor (psycopg/psycopg#101), but it wouldn't be the most efficient path.

[jerch]

Ah sorry if my wording is wrong - yes I use the Json() wrapper and meant the Json(None) --> 'null'::jsonb conversion.

Just realized, that this ambiguity already exists in python to some extend - most of the time None is treated as "nothing" during control flow, but with json its object nature materializes into a "nothing-object" by all json converters. And we have nothing to distinguish these none meanings anymore. Hmm.

Yeah I get that the "do you really want to become null?" distinction is a perf smell, I kinda have the same issue in the opposite way (treating all None's for COPY FROM uniquely to avoid encoding costs, thus I stumbled over the difference for a Json() wrapped value). Already fixed it by prefiltering Nones before applying the wrapper, just was curious, why the wrapper does not use sql NULL in the first place.

Thx alot!