Incorrect Decimal('Infinity') adaptation

[hordiienko-v]

• OS: Ubuntu
• Psycopg version: 2.9.9
• Python version: 3.9
• PostgreSQL version: 16.2
• pip version: 21.2.4

Since v14, PostgreSQL NUMERIC type supports Infinity value. But for now, psycopg2 adapts Infinity as 'NaN'::numeric. This can be avoided by creating custom type adapter that wraps decimal value in quotes, but still, adapting to NaN seems like outdated logic.

from decimal import Decimal

from psycopg2 import connect
from psycopg2._psycopg import connection, cursor

if __name__ == "__main__":
    conn: connection
    curr: cursor

    with connect("dbname=test_db user=test_user password=test_user") as conn:
        with conn.cursor() as curr:
            q = curr.mogrify(
                "INSERT INTO test VALUES (%s), (%s)",
                [Decimal("1"), Decimal("Infinity")],
            )
            print(q)
            # b"INSERT INTO test VALUES (1), ('NaN'::numeric)"

[dvarrazzo]

Thank you for the report. The solution is probably little enough invasive that we can provide it in the next bugfix release.

Psycopg 3 already does the right thing:

>>> import psycopg.sql
>>> from decimal import Decimal
>>> psycopg.sql.quote(Decimal("Infinity"))
"'Infinity'::numeric"

[edgarrmondragon]

Got a fix: #1735

[dvarrazzo]

I am reconsidering the opportunity of changing this behaviour. Yes, converting inf -> nan is not correct but otoh I don't know if there are applications out there assuming it...

[dvarrazzo]

As discussed, we prefer to not make this change in psycopg 2.