DataError:将多维数组插入PostgreSQL中时,数组文字格式错误 [英] DataError: Malformed array literal when inserting multidimensional array into PostgreSQL
问题描述
这是我的表格的简化版本:
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)
在尝试插入以下内容时多维数组插入表中时,出现DataError告诉我我的数组格式错误。
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']]
}
我的印象是,无论如何创建,Postgres的ARRAY类型都是多维的( http://thread.gmane.org/gmane.comp.python.sqlalchemy.user/31186 ),为什么会发生此错误?
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?
推荐答案
PostgreSQL中的数组可以是多维的,但是所有元素必须具有相同的维数( http://www.postgresql.org/docs/9.4/interactive/arrays.html ),因此您可以编写:
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}}"
,但不是:
SELECT ARRAY[ARRAY[1,2], ARRAY[3]]
ERROR: multidimensional arrays must have array expressions with matching dimensions
这篇关于DataError:将多维数组插入PostgreSQL中时,数组文字格式错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!