DataError: Malformed array literal when inserting multidimensional array into PostgreSQL

Question

This is a simplified version of my table:

class Alert(Base):
    __tablename__ = 'alert'

    id = Column(Integer, primary_key=True)
    created_on = Column(DateTime(timezone=True))
    category = Column(Unicode, nullable=False)
    parameters = Column(ARRAY(Unicode), nullable=True)

When trying to insert the following multidimensional array into the table, I get a DataError telling me that my array is malformed.

DataError: (DataError) malformed array literal: "numbers"
LINE 1: ...RRAY[ARRAY['key', 'sXl5SoNh0KY'], ARRAY['nu...
                                                   ^
DETAIL:  Array value must start with "{" or dimension information.
'INSERT INTO alert (created_on, category, parameters) 
VALUES (%(created_on)s, %(category)s, %(parameters)s) 
RETURNING alert.id' {
'created_on': datetime.datetime(2015, 6, 24, 1, 52, 30, 631330, tzinfo=<UTC>)
'category': u'new_cases',
'parameters':
    [[u'key', u'sXl5SoNh0KY'],
    ['numbers', [u'8129431', u'8669290', u'8754131', u'8871813', u'8927606']],
    ['status', 'all']]
}

I was under the impression that Postgres' ARRAY type is multidimensional regardless of how it is created (http://thread.gmane.org/gmane.comp.python.sqlalchemy.user/31186), so why is this error occurring?

Renzo · Accepted Answer · 2015-06-25 04:37:28Z

4

Arrays in PostgreSQL can be multidimensional, but all the elements must have the same number of dimensions (http://www.postgresql.org/docs/9.4/interactive/arrays.html), so you can write:

SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];

=> "{{1,2},{3,4}}"

but not:

SELECT ARRAY[ARRAY[1,2], ARRAY[3]]

ERROR:  multidimensional arrays must have array expressions with matching dimensions

edited Jun 25, 2015 at 4:37

answered Jun 24, 2015 at 5:44

Renzo

27.6k5 gold badges54 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

DataError: Malformed array literal when inserting multidimensional array into PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related