postgres=# select * from rock_band;
name | members
--------------+---------------------------
Led Zeppelin | {Page,Plant,Jones,Bonham}
(1 row)
另一种方法是插入的时候使用数组的构造器:
INSERT INTO rock_band
VALUES
('Pink Floyd',
ARRAY['Barrett', 'Gilmour']
)
当使用数组构造器的时候, 数组元素是用单引号.
postgres=# select * from rock_band;
name | members
--------------+---------------------------
Led Zeppelin | {Page,Plant,Jones,Bonham}
Pink Floyd | {Barrett,Gilmour}
(2 rows)
访问数组类型
数组类型的值可以通过下标和切片的方式访问:
postgres=# select name from rock_band where members[2] = 'Plant';
name
--------------
Led Zeppelin
(1 row)
postgres=# select members[1:2] from rock_band;
members
-------------------
{Page,Plant}
{Barrett,Gilmour}
(2 rows)
修改数组值
数组字段可以更新某个数组元素或者整个值:
更新单个元素:
postgres=# UPDATE rock_band set members[2] = 'Waters' where name = 'Pink Floyd';
UPDATE 1
postgres=# select * from rock_band where name = 'Pink Floyd';
name | members
------------+------------------
Pink Floyd | {Barrett,Waters}
(1 row)
更新整个字段值:
postgres=# UPDATE rock_band set members = '{"Mason", "Wright", "Gilmour"}' where name = 'Pink Floyd';
UPDATE 1
postgres=# select * from rock_band where name = 'Pink Floyd';
name | members
------------+------------------------
Pink Floyd | {Mason,Wright,Gilmour}
(1 row)
在数组中搜索
要在数组中查找某个特定元素值, 可以使用ANY关键词.
postgres=# select name from rock_band where 'Mason' = ANY(members);
name ------------
Pink Floyd
(1 row)
postgres=# select name from rock_band where 'Barrett' = ANY(members);
name ------
(0 rows)