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字段