Inconsistent exceptions when failing to adapt NumPy 2.0 types

[emmanuelito]

Please complete the following information:

• OS: Linux Debian 12.6
• Psycopg version: 2.9.9
• Python version: 3.11.2
• PostgreSQL version: 15.7
• pip version: 23.0.1

Describe the bug

Migrating from Numpy 1.26. The following was working:

import psycopg2
import numpy as np
cnx = psycopg2.connect("dbname=XXX")
cursor = cnx.cursor()
cursor.execute("""CREATE TABLE test_np (val double precision);""")
cursor.execute(
    "INSERT INTO test_np (val) VALUES (%(val)s);",
    {"val": np.float64(1.0)},
)
cnx.commit()

Under NumPy 2.0.1 the code raises

psycopg2.errors.InvalidSchemaName: schema "np" does not exist
LINE 1: INSERT INTO test_np (val) VALUES (np.float64(1.0));

which is quite obscure.

Whereas if we change the type to np.float32we gat another, more explicit, exception:

psycopg2.ProgrammingError: can't adapt type 'numpy.float32'

By the way, the above code (but not the strange exception message) is fixed by adding

from psycopg2.extensions import register_adapter, AsIs

def adapt_numpy_float(numpy_float):
    return AsIs(numpy_float)

register_adapter(np.float32, adapt_numpy_float)
register_adapter(np.float64, adapt_numpy_float)

[dvarrazzo]

In Numpy 2 it looks like they changed the repr() of these types, as well as the MRO. float64 fails for the different repr(), float32 fails because it doesn't inherit from float.

Numpy 1.x support was only accidental and not tested; it worked because these types had both repr and MRO compatible with Python floats. Therefore, registering an adapter is the only supported way. Maybe, in your adapter, return AsIs(str(numpy_float)) would be better.

Psycopg 3.2 has official support for numpy scalars and it is tested for both numpy version <2 and 2. We are not planning to add builtin support in psycopg2.