PostgreSQL数组使用
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了PostgreSQL数组使用，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7346字，纯文字阅读大概需要11分钟。
内容图文
不一样的维度元素长度定义在数据库中的实际存储都是一样的，数组元素的长度和类型必须要保持一致，并且以中括号来表示。
合理的：
array[1,2] --一维数组
array[[1,2],[3,5]] --二维数组
‘{99,889}‘
不合理的:
array[[1,2],[3]] --元素长度不一致
array[[1,2],[‘Kenyon‘,‘good‘]] --类型不匹配
[postgres@localhost ~]$ psql psql (9.2.4) Type "help" for help. postgres=# create table t_kenyon(id serial primary key,items int[]); NOTICE: CREATE TABLE will create implicit sequence "t_kenyon_id_seq" for serial column "t_kenyon.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_kenyon_pkey" for table "t_kenyon" CREATE TABLE postgres=# \d+ t_kenyon Table "public.t_kenyon" Column | Type | Modifiers | Storage | Stats target | Description --------+-----------+-------------------------------------------------------+----------+--------------+------------- id | integer | not null default nextval(‘t_kenyon_id_seq‘::regclass) | plain | | items | integer[] | | extended | | Indexes: "t_kenyon_pkey" PRIMARY KEY, btree (id) Has OIDs: no postgres=# create table t_ken(id serial primary key,items int[4]); NOTICE: CREATE TABLE will create implicit sequence "t_ken_id_seq" for serial column "t_ken.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_ken_pkey" for table "t_ken" CREATE TABLE postgres=# \d+ t_ken Table "public.t_ken" Column | Type | Modifiers | Storage | Stats target | Description --------+-----------+----------------------------------------------------+----------+--------------+------------- id | integer | not null default nextval(‘t_ken_id_seq‘::regclass) | plain | | items | integer[] | | extended | | Indexes: "t_ken_pkey" PRIMARY KEY, btree (id) Has OIDs: no 数组的存储方式是extended的。
2.数组操作
a.数据插入（两种方式）
postgres=# insert into t_kenyon(items) values(‘{1,2}‘); INSERT 0 1 postgres=# insert into t_kenyon(items) values(‘{3,4,5}‘); INSERT 0 1 postgres=# insert into t_kenyon(items) values(array[6,7,8,9]); INSERT 0 1 postgres=# select * from t_kenyon; id | items ----+----------- 1 | {1,2} 2 | {3,4,5} 3 | {6,7,8,9} (3 rows)
b.数据删除
postgres=# delete from t_kenyon where id = 3; DELETE 1 postgres=# delete from t_kenyon where items[1] = 4; DELETE 0 postgres=# delete from t_kenyon where items[1] = 3; DELETE 1
c.数据更新
往后追加 postgres=# update t_kenyon set items = items||7; UPDATE 1 postgres=# select * from t_kenyon; id | items ----+--------- 1 | {1,2,7} (1 row) postgres=# update t_kenyon set items = items||‘{99,66}‘; UPDATE 1 postgres=# select * from t_kenyon; id | items ----+------------------ 1 | {1,2,7,55,99,66} (1 row) 往前插 postgres=# update t_kenyon set items = array_prepend(55,items) ; UPDATE 1 postgres=# select * from t_kenyon; id | items ----+--------------------- 1 | {55,1,2,7,55,99,66} (1 row)
d.数据查询
postgres=# insert into t_kenyon(items) values(‘{3,4,5}‘); INSERT 0 1 postgres=# select * from t_kenyon where id = 1; id | items ----+--------------------- 1 | {55,1,2,7,55,99,66} (1 row) postgres=# select * from t_kenyon where items[1] = 55; id | items ----+--------------------- 1 | {55,1,2,7,55,99,66} (1 row) postgres=# select * from t_kenyon where items[3] = 5; id | items ----+--------- 4 | {3,4,5} (1 row) postgres=# select items[1],items[3],items[4] from t_kenyon; items | items | items -------+-------+------- 55 | 2 | 7 3 | 5 | (2 rows) postgres=# select unnest(items) from t_kenyon where id = 4; unnest -------- 3 4 5 (3 rows)
e.数组比较
postgres=# select ARRAY[1,2,3] <= ARRAY[1,2,3]; ?column? ---------- t (1 row)
f.数组字段类型转换
postgres=# select array[[‘11‘,‘12‘],[‘23‘,‘34‘]]::int[]; array ------------------- {{11,12},{23,34}} (1 row) postgres=# select array[[11,12],[23,34]]::text[]; array ------------------- {{11,12},{23,34}} (1 row)
3.数组索引
postgres=# create table t_kenyon(id int,items int[]); CREATE TABLE postgres=# insert into t_kenyon values(1,‘{1,2,3}‘); INSERT 0 1 postgres=# insert into t_kenyon values(1,‘{2,4}‘); INSERT 0 1 postgres=# insert into t_kenyon values(1,‘{34,7,8}‘); INSERT 0 1 postgres=# insert into t_kenyon values(1,‘{99,12}‘); INSERT 0 1 postgres=# create index idx_t_kenyon on t_kenyon using gin(items); CREATE INDEX postgres=# set enable_seqscan = off; postgres=# explain select * from t_kenyon where items@>array[2]; QUERY PLAN --------------------------------------------------------------------------- Bitmap Heap Scan on t_kenyon (cost=8.00..12.01 rows=1 width=36) Recheck Cond: (items @> ‘{2}‘::integer[]) -> Bitmap Index Scan on idx_t_kenyon (cost=0.00..8.00 rows=1 width=0) Index Cond: (items @> ‘{2}‘::integer[]) (4 rows)
1.数组的定义
不一样的维度元素长度定义在数据库中的实际存储都是一样的，数组元素的长度和类型必须要保持一致，并且以中括号来表示。
合理的：
array[1,2] --一维数组
array[[1,2],[3,5]] --二维数组
‘{99,889}‘
不合理的:
array[[1,2],[3]] --元素长度不一致
array[[1,2],[‘Kenyon‘,‘good‘]] --类型不匹配
[postgres@localhost ~]$ psql
psql (9.2.4)
Type "help" for help.
postgres=# create table t_kenyon(id serial primary key,items int[]);
NOTICE: CREATE TABLE will create implicit sequence "t_kenyon_id_seq" for serial column "t_kenyon.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_kenyon_pkey" for table "t_kenyon"
CREATE TABLE
postgres=# \d+ t_kenyon
Table "public.t_kenyon"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------+-------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval(‘t_kenyon_id_seq‘::regclass) | plain | |
items | integer[] | | extended | |
Indexes:
"t_kenyon_pkey" PRIMARY KEY, btree (id)
Has OIDs: no
postgres=# create table t_ken(id serial primary key,items int[4]);
NOTICE: CREATE TABLE will create implicit sequence "t_ken_id_seq" for serial column "t_ken.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_ken_pkey" for table "t_ken"
CREATE TABLE
postgres=# \d+ t_ken
Table "public.t_ken"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------+----------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval(‘t_ken_id_seq‘::regclass) | plain | |
items | integer[] | | extended | |
Indexes:
"t_ken_pkey" PRIMARY KEY, btree (id)
Has OIDs: no
数组的存储方式是extended的。PostgreSQL数组使用
标签：repeat postgre help anti ora 存储 creat _id ken
本文系统来源：http://www.cnblogs.com/YYRise/p/6268912.html
内容总结
以上是互联网集市为您收集整理的PostgreSQL数组使用全部内容，希望文章能够帮你解决PostgreSQL数组使用所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】