current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

I'm creating a schema for my DB using PostgreSQL and SQLAlchemy in declarative base and ran into a question that I think is answered but I'm somehow getting the syntax wrong.

I'm thinking of having an entry that's a multidimensional array (I only need 2D, but I figured I'd ask for an n-dimentional array solution in case I run into other issues). I essentially need to have some_field[string][string] but am not sure of how to do that.

class SomeTable(Base):
    __tablename__ = 'some_table'
   multi_d_array = Column(postgresql.ARRAY(String)) #How do I make 2D+?

With an earlier version dimensionality was apparently unenforced (link: PostgreSQL multidimensional arrays in SQLAlchemy), but apparently this was patched (link: https://groups.google.com/forum/?fromgroups=#!topic/sqlalchemy/w4-nbMdxxUg). The documentation still says it's unenforced (link: http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html) but I just wanted to make sure the code works alright before having my DB blow up.

Thanks for helping me with my newbie question.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Postgresql itself does not enforce the dimensionality of the array - all arrays are N-dimensional even if you specify it with a specific number of dimensions. From the linked email:

http://www.postgresql.org/docs/9.1/static/arrays.html

"The current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior."

In SQLAlchemy 0.8, the ARRAY type now includes an optional parameter "dimension=N" whereby you can give it the specific number of dimensions, which will allow the SQLAlchemy type to process incoming array data more quickly in Python (it doesn't need to guess the number of dimensions), and also cause it to emit DDL (i.e. CREATE TABLE) with that number of dimensions specifically, but without this parameter everything would still work as is. The email you refer to is a user who wanted it to specifically say "INTEGER [][]" with that exact number of dimensions when emitting DDL, but it's really not necessary.

share|improve this answer
    
Thanks, I just needed a reality check from someone more knowledgeable -- and to confirm that by implementing it without specifying dimensionality would not cause it to break in future iterations. –  binarysolo Dec 15 '12 at 7:19
    
Side question in case you knew: what if I wanted something that was ARRAY[string][int][some_type] -- where the different dimensions are differently typed? –  binarysolo Dec 15 '12 at 7:22
    
I'm having trouble visualizing what that means. an array is a grid of scalars, the dimensions are coordinate scales. I don't see how an additional type gets associated with a dimension. seems like you're thinking of an associative array, perhaps. –  zzzeek Dec 15 '12 at 16:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.