"ProgrammingError: cannot determine type of empty array" even with proper model declaration?

[Michael Nachtigal]

Hello, all,

I'm receiving this error:

ProgrammingError: (ProgrammingError) cannot determine type of empty array

It looks like this is happening because the default value for column in new instances of one of my models is being initialized to [] (an empty array, no inner type), despite my column being defined like this in the model class:

mycol = Column('mycol',
               ARRAY(Integer),
               server_default=text('ARRAY[]::integer[]'),
               default=array([], type_=Integer),
               nullable=False)

I've tried it with both the "default" and "server_default" kwargs, with only the "default" kwarg, and with only the "server_default" kwargs, and it seems not to make a difference (and I'm not sure really which combination of arguments would be most appropriate here, anyway, but that's not the primary reason for my question). The column in the database is defined like this:

mycol          integer array NOT NULL

I would appreciate any kind of help or information anyone could provide to help me solve this.

Thanks,
Mike

[Gunnlaugur Thor Briem]

Hi,

1. the server_default=... argument just says what default to define for the column *on creation* --- it has no effect if the table already exists. To apply the default to an existing table, you need to execute something like:

ALTER TABLE mytbl ALTER COLUMN mycol ADD DEFAULT ARRAY[]::integer[];

2. the server-side default has no effect if you configure SQLAlchemy to always specify a value for the column (which is what the default=... argument does).

So you could either (a) alter the table and skip the default=... argument, or (b) change the default=... argument to literal SQL with an explicit cast, e.g. default=literal_column("'{}'::integer[]") (because array() doesn't get compiled with an explicit typecast, even if you give it an explicit type_ ... maybe it should, when the array is empty.)

Cheers,

Gulli

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[Michael Nachtigal]

Gulli,

Thank you so much for the helpful information. I think that the "type_" argument should cause an explicit cast if it's on a default array empty list value; that very much was my expectation, unfortunately!

I've discovered what appears to be very strange & unexpected behavior:

mycol = Column('mycol', ARRAY(Integer), default=[], nullable=False)  # does work
mycol = Column('mycol', ARRAY(Integer), default=array([], type_=Integer), nullable=False)  # doesn't work (?!)

It's very strange, because adding more information to the default argument value causes things to break, whereas a simple empty array ends up acquiring whatever ARRAY casts it needs to work. In the end we decided to go with the following solution, which is similar to what you suggested and does appear to work:

mycol = Column('mycol', ARRAY(Integer), default=cast(array([], type_=Integer), ARRAY(Integer)), nullable=False)

Thanks again for your time and reply,
Mike

---

From: sqlal...@googlegroups.com [sqlal...@googlegroups.com] on behalf of Gunnlaugur Thor Briem [gunnl...@gmail.com]
Sent: Tuesday, December 03, 2013 8:35 AM
To: sqlalchemy
Subject: Re: [sqlalchemy] "ProgrammingError: cannot determine type of empty array" even with proper model declaration?

[Michael Bayer]

it seems like psycopg2 is doing some odd pattern of guessing vs. not here, if default=[] works and default=array([]) does not, not sure why that would be, examining the SQL output (if not the PG logs themselves with statement logging turned on to see what psycopg2 is actually sending) would reveal what’s going on fully.