Not possible to insert Nulls in Postgresql arrays

[Eoghan Murray]

Question asked by someone else: http://stackoverflow.com/questions/22485971/python-sqlalchemy-insert-array-postgres-with-null-values-not-possible

The SQL executed by postgresql is

CREATE TABLE test_table (a bigint[]);

INSERT INTO test_table VALUES (ARRAY[12]);  -- this is fine

INSERT INTO test_table VALUES (ARRAY[NULL]);  -- this is what SQLAlchemy/psycopg generates
ERROR:  column "a" is of type bigint[] but expression is of type text[]

We need either

INSERT INTO test_table VALUES (ARRAY[NULL]::bigint[]);

or

INSERT INTO test_table VALUES ('{NULL}');

to be generated.

I've scoured the SQLAlchemy source, but can't find where the square brackets or 'ARRAY' get added, are they added by psycopg2?

[Eoghan Murray]

This could be how it could be tested in sqlalchemy/test/dialect/postgresql/test_types.py:

def test_insert_array(self):
    arrtable = self.tables.arrtable
    arrtable.insert().execute(intarr=[1, 2, 3, None], strarr=[util.u('abc'),
                                                        util.u('def')])
    results = arrtable.select().execute().fetchall()
    eq_(len(results), 1)
    eq_(results[0]['intarr'], [1, 2, 3, None])
    eq_(results[0]['strarr'], [util.u('abc'), util.u('def')])

[Mike Bayer]

I'm not reproducing this issue. Test case (note bugs should always include test cases, see the guidelines):

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'

    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 = [None]
s.add(member)
s.commit()

output:

BEGIN (implicit)
2017-02-16 12:54:10,665 INFO sqlalchemy.engine.base.Engine INSERT INTO test (pay) VALUES (%(pay)s) RETURNING test.id
2017-02-16 12:54:10,665 INFO sqlalchemy.engine.base.Engine {'pay': [None]}
2017-02-16 12:54:10,667 INFO sqlalchemy.engine.base.Engine COMMIT

are you using psycopg2 latest ?

[Mike Bayer]

• add test for inserting PG array w/ NULL, references #3916

Change-Id: I87be274c1ba019b41744a5a76c1b5e9334564ec8

→ <<cset 42bb86568919>>

[Mike Bayer]

that test passes. just pushed it up, we'll see if CI passes it also.

[Mike Bayer]

Works in CI. If you can't provide for me specifics on how to reproduce I'll have to close.

[Eoghan Murray]

Can confirm psycopg2 upgrade from 2.4.6 to 2.6.2 fixes this. Never even occurred to me to do this. Apologies for lack of test case, I did look in the tests folder to try to find a simple test that I could adapt but it was taking me ages. Thanks for taking the time and the quick response; and apologies, will be more thorough with versioning and testing if I ever think I've found something again!

[Eoghan Murray]

psycopg2 upgrade solved it

[Mike Bayer]

OK great, often these ARRAY/JSON things are on my end so I wasn't sure at first if it would be just psycopg2.