Im working with multidimensional arrays, and I have noticed that postgres needs casted expression to insert values, eg.:
CREATE TABLE test (
pay integer[]
);
CREATE TABLE
INSERT INTO test values (ARRAY[NULL]);
ERROR: column "pay" is of type integer[] but expression is of type text[]
LINE 1: INSERT INTO test values (ARRAY[NULL]);
^
HINT: You will need to rewrite or cast the expression.
INSERT INTO test values (ARRAY[NULL::integer]); -- How to do this on SqlAlchemy ?
INSERT 0 1 -- ARRAY[NULL]::integer[] would also works
This is what SqlAlchemy is doing when I add an object, it doesn't make type casting if VALUE is NULL. Here is part of my code:
from sqlalchemy.dialects.postgresql import ARRAY
class Test (Base):
__tablename__ = 'test'
pay = Column(ARRAY(Integer))
member = Test()
member.pay = [None]
session.add(member)
session.commit()
And then at postgres log I get:
ERROR: column "pay" is of type integer[] but expression is of type text[] at character 26
HINT: You will need to rewrite or cast the expression.
STATEMENT: INSERT INTO test (pay) VALUES (ARRAY[NULL]); -- See ?? No casting from SqlAlchemy
So the question is: What can I do for SqlAlchemy makes the type casting also when values within list are None? Perhaps some custom type implementation ?
