Python SqlAlchemy insert ARRAY (postgres) with NULL values not possible?

Question

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 ?

I've created an issue: bitbucket.org/zzzeek/sqlalchemy/issues/3916/… — EoghanM
– EoghanM, Commented Feb 16, 2017 at 17:49
can't reproduce; see the bug report. make sure you're on recent psycopg2 and sqlalchemy version. — zzzeek
– zzzeek, Commented Feb 16, 2017 at 18:02

EoghanM · Accepted Answer · 2017-02-16 21:14:29Z

2

It seems to have been an omission in psycopg2; upgrading from 2.4.6 to 2.6.2 fixed it. Thanks of course to SQLA author @zzzeek

answered Feb 16, 2017 at 21:14

EoghanM

27.4k25 gold badges95 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sam · Accepted Answer · 2014-09-17 15:29:29Z

0

It looks like you are trying to insert

VALUES (ARRAY[NULL])

which it is reading as

VALUES ('ARRAY[NULL]')

while what you actually want to insert is

VALUES ({NULL})

or

VALUES ({})

i.e. an empty array.

Check out the PostgreSQL manual's page on arrays for more details about array behaviour and related examples

answered Sep 17, 2014 at 15:29

Sam

1258 bronze badges

2 Comments

user1538560 Over a year ago

No, i do want to store {NULL}, but the question here is not how to do that in postgres, but on sqlalchemy

Sam Over a year ago

Ok, I was misunderstanding a bit, sorry. You are trying to send it [none] which is a text string, but if you give it [] which is a null string instead, that might work.

Collectives™ on Stack Overflow

Python SqlAlchemy insert ARRAY (postgres) with NULL values not possible?

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related