

PostgreSQL array field with Django - craigkerstiens
http://www.niwi.be/2012/10/07/postgresql-array-fields-with-django/

======
ashray
Could you tell me the specific advantages that this may have over say, json
data in a text field ?

Is it faster to:

\- Convert an array into json and push it into a text field

\- Retrieve it the same way

Or use this ?

Also, are there any distinct full-text indexing advantages ? I know more MySQL
than Postgres but am eager to learn about it since it looks way more powerful!
:)

~~~
bdr
One advantage is filtering query results at the DB layer. For example, you
could compare elements within an array, test a particular index of the array,
or see if a value is present anywhere in the array. See
<http://www.postgresql.org/docs/9.2/static/arrays.html>.

~~~
ashray
Thanks! On further thinking about my question I realized that anything at the
DB layer (Postgres) will probably be faster than doing it in Python because
it's written in C. (re: the json load and dump part of my question..)

~~~
dagw
I don't know if the default json library in python is written C or not (never
had a problem where json decoding was even close to a bottleneck), but there
are at least a couple of very high performance json libraries for python
written in C, so that shouldn't be much of a problem.

------
zzzeek
SQLAlchemy has had a PG ARRAY type for a few years, and 0.8 now has full
expression support including the ability to use regular Python array indexes
and slices:

    
    
        select([mytable.c.data[5], mytable.c.data[2:7]])
    

as well as array literals in expressions:

    
    
        stmt = select([
                    array([1,2]) + array([3,4,5])
                ])
    

We'll soon be adding hstore (and JSON) to tip as well, right now hstore is
available as a separate extension:

[https://bitbucket.org/audriusk/hstore/src/e0b4f7987ebe/examp...](https://bitbucket.org/audriusk/hstore/src/e0b4f7987ebe/example_orm.py#cl-48)

~~~
dguaraglia
SQLAlchemy is an amazing library, and I wish I didn't need the Django Admin
(our project is still depending on it for a bunch of... well, admin work) so I
could just use SQLAlchemy everywhere.

------
plq
I'm working on a similar thing for Spyne. It's not fully working (nor
released) yet but tests are here:
[https://github.com/arskom/spyne/blob/master/spyne/test/test_...](https://github.com/arskom/spyne/blob/master/spyne/test/test_sqlalchemy.py#L363)

Basically, complex objects can be stored as json and xml as well as using one-
to-one, one-to-many and many-to-many table configurations. Native postgres
arrays are also in the to-do list, but probably won't make it in time for 2.9.

------
hcarvalhoalves
This is particularly useful to implement trees with materialized paths [1].
Since you can filter / order by those array fields, you can implement very
efficient trees that do anything in one query, with all the benefits of
relational algebra / DBMS. No need to resort to document stores to implement
trees.

[1] [http://justcramer.com/2012/04/08/using-arrays-as-
materialize...](http://justcramer.com/2012/04/08/using-arrays-as-materialized-
paths-in-postgres/)

