[Sqlalchemy-tickets] Issue #4244: Impossible to insert subarray of nulls in multidimensional arrays (zzzeek/sqlalchemy)

[Eoghan M. <iss...@bi...>]

New issue 4244: Impossible to insert subarray of nulls in multidimensional arrays
https://bitbucket.org/zzzeek/sqlalchemy/issues/4244/impossible-to-insert-subarray-of-nulls-in

Eoghan Murray:

Very similar issue to https://bitbucket.org/zzzeek/sqlalchemy/issues/3916/not-possible-to-insert-nulls-in-postgresql which was solved by an upgrade of psycopg2 to 2.6.2.

I'm experiencing the following issue with latest psycopg2  2.7.4 and sqlalchemy 1.2.7

Test case:


```
#!python

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import ARRAY

Base = declarative_base()


class Test (Base):

    __tablename__ = 'test_subarray_nulls'

    id = Column(Integer, primary_key=True)
    pay = Column(ARRAY(Integer))

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)

s = Session(e)
member = Test()
member.pay = [[1.1, 1.2]]  # fine
member.pay = [[None, 2.2]]  # fine
member.pay = [[3.1, None]] # fine
member.pay = [[4.1, 4.2], [None, None]] # fine
member.pay = [[None, None]] # broken: sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "pay" is of type integer[] but expression is of type text[]
s.add(member)
s.commit()

```

Output:
```
[snip]
2018-04-26 14:08:34,264 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-04-26 14:08:34,265 INFO sqlalchemy.engine.base.Engine INSERT INTO test_psycopg_4 (pay) VALUES (%(pay)s) RETURNING test_psycopg_4.id
2018-04-26 14:08:34,265 INFO sqlalchemy.engine.base.Engine {'pay': [[None, None]]}
2018-04-26 14:08:34,266 INFO sqlalchemy.engine.base.Engine ROLLBACK
[snip (traceback)]
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "pay" is of type integer[] but expression is of type text[]
LINE 1: INSERT INTO test_psycopg_4 (pay) VALUES (ARRAY['{NULL, NULL}...
                                                 ^
HINT:  You will need to rewrite or cast the expression.
```