- changed milestone to 1.1
- changed title to support direct mult-dimensional ARRAY access based on explicit dimensions
- marked as enhancement
support direct mult-dimensional ARRAY access based on explicit dimensions
Here's just an example:
weights = Column(ARRAY(DECIMAL(precision=2, asdecimal=False), dimensions=2), nullable=False, default=[])
then in query:
CallServiceCampaign.weights[
func.idx(CallServiceCampaign.goods_ids, literal(goods_id))
][1] != None,
produces:
NotImplementedError: Operator 'getitem' is not supported on this expression
I think this is because https://bitbucket.org/zzzeek/sqlalchemy/src/447ee0af1d2fbb95f2f1244de301f2fe4a87a72f/lib/sqlalchemy/dialects/postgresql/base.py?at=rel_1_0_6#cl-911 doesn't handle dimensions
parameter and always returns the underlying array's class on the first non-sliced reference.
Comments (5)
-
repo owner -
repo owner - add a note, references
#3487
→ <<cset eed8bcef3ae1>>
- add a note, references
-
repo owner see also
#3499, where we have similar issues with JSON. We need to determine for indexable types a clear picture for what type the return values should be. For ARRAY it is fairly simple. For a JSON, perhaps some option to specify a schema, though that seems quite ominous. -
repo owner all of the above is working as of https://bitbucket.org/zzzeek/sqlalchemy/src/39adada25d71ac33d2a468cc19eb61c0fdfa9280/?at=ticket_3499, not merged yet
-
repo owner - changed status to resolved
- merge of ticket_3499 indexed access branch
- The "hashable" flag on special datatypes such as :class:
.postgresql.ARRAY
, :class:.postgresql.JSON
and :class:.postgresql.HSTORE
is now set to False, which allows these types to be fetchable in ORM queries that include entities within the row. fixes#3499 - The Postgresql :class:
.postgresql.ARRAY
type now supports multidimensional indexed access, e.g. expressions such assomecol[5][6]
without any need for explicit casts or type coercions, provided that the :paramref:.postgresql.ARRAY.dimensions
parameter is set to the desired number of dimensions. fixes#3487 - The return type for the :class:
.postgresql.JSON
and :class:.postgresql.JSONB
when using indexed access has been fixed to work like Postgresql itself, and returns an expression that itself is of type :class:.postgresql.JSON
or :class:.postgresql.JSONB
. Previously, the accessor would return :class:.NullType
which disallowed subsequent JSON-like operators to be used. part of fixes#3503 - The :class:
.postgresql.JSON
, :class:.postgresql.JSONB
and :class:.postgresql.HSTORE
datatypes now allow full control over the return type from an indexed textual access operation, eithercolumn[someindex].astext
for a JSON type orcolumn[someindex]
for an HSTORE type, via the :paramref:.postgresql.JSON.astext_type
and :paramref:.postgresql.HSTORE.text_type
parameters. also part of fixes#3503 - The :attr:
.postgresql.JSON.Comparator.astext
modifier no longer calls upon :meth:.ColumnElement.cast
implicitly, as PG's JSON/JSONB types allow cross-casting between each other as well. Code that makes use of :meth:.ColumnElement.cast
on JSON indexed access, e.g.col[someindex].cast(Integer)
, will need to be changed to call :attr:.postgresql.JSON.Comparator.astext
explicitly. This is part of the refactor in references#3503for consistency in operator use.
→ <<cset ceeb033054f0>>
- Log in to comment
Note that a pg ARRAY with no explicit dimensions is N-dimensional, and I'm not sure if that can be handled without explicit casts.
Below, please use the ARRAY_D workaround for now.