在django 1.6中使用postgresql的array(1.8版本已直接支持)

1、postgresql中的array字段

类型声明：

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[], // or integer ARRAY[4] or integer ARRAY,
    schedule        text[][]
);

数组的维度不起作用，只能当作文档使用。

插入：

方式1：

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

注意，大括号{}需要用单引号包起来。

方式2：

INSERT INTO sal_emp
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);

有ARRAY关键字就不需要单引号了，但注意，ARRAY后面没有小括号。

访问/修改：

按维度访问：

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

支持类python的slice：

SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{meeting},{training}}
(1 row)

注意，数组任意维度出现slice(:)，则其他维度都会被当作slice看待，比如：

SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';

                 schedule
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)

此处的第二维度的[2]就当作了[1:2]

更新整个数组：

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

更新某一维度：

UPDATE sal_emp SET pay_by_quarter[4] = 15000
    WHERE name = 'Bill';

UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
    WHERE name = 'Carol';

选择：

是否存在，类似于in操作符：

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

两个数组是否交集：

select name from sal_emp where '{10000,30000}' && pay_by_quarter;

类型转换

select name from sal_emp where '{"meeting","consulting"}'::text[] && schedule[1:1];

2、在DJango中操作

声明：

DJango 1.6中不支持字段类型django.contrib.postgres.fields.ArrayField，所以要访问数组字段，可以增加一个冗余字段，比如：

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[], // or integer ARRAY[4] or integer ARRAY,
    pay_by_quarter_s char varying);

update sal_emp set pay_by_quarter_s = pay_by_quarter_s;

在DJango中可以忽略pay_by_quarter字段，这样写：

class SalEmp(models.Model):
    name = models.CharField(max_length=-1, blank=True)
    pay_by_quarter = models.CharField(max_length=-1, blank=True, db_column='pay_by_quarter_s')
    
    class Meta:
        db_table = 'sal_emp'

插入：

conn = psycopg2.connect(database="mycloud", user="postgres", password="")
cur = conn.cursor()

cur.execute("insert into sal_emp values(%s,%s)",['Bill',[25000,25000,27000,27000]])

conn.commit()
cur.close()
conn.close()

python中的字符串可以自动转为postgresql中的数组类型，不管数组是整数数组还是字符串数组，都可以用%s来表示。

查询：

wheres = ["%s && years"]
wheres_param = [[10000,30000]]
dao = SalEmp.objects.extra(where=wheres, params=wheres_param)

dao为queryset类型的。

3、参考

Basic module usage:postgresql与python中类型对应关系，如 py:list<--->pg:array

PostgreSQL specific model fields:新版的DJango中增加的Arrayfield字段