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.
On Dec 3, 2013, at 9:22 AM, Michael Nachtigal
<michael.nachti...@catalinamarketing.com> wrote:
> 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: sqlalchemy@googlegroups.com [sqlalchemy@googlegroups.com] on behalf of
> Gunnlaugur Thor Briem [gunnlau...@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?
>
> 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
>
>
>
> On Tue, Dec 3, 2013 at 1:10 PM, Michael Nachtigal
> <michael.nachti...@catalinamarketing.com>wrote:
> 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
> --
> 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 tosqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
> --
> 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 tosqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
