PostgreSQL数组使用
来源:互联网 发布:枯叶龟多少钱一只淘宝 编辑:程序博客网 时间:2024/06/06 10:40
开发的语言有数组的概念,对应于postgresql也有相关的数据字段类型,数组是英文array的翻译,可以定义一维,二维甚至更多维度,数学上跟矩阵很类似。在postgres里面可以直接存储使用,某些场景下使用很方便,也很强大。
环境:
OS:CentOS 6.2
DB: PostgreSQL 9.2.4
1.数组的定义
不一样的维度元素长度定义在数据库中的实际存储都是一样的,数组元素的长度和类型必须要保持一致,并且以中括号来表示。
合理的:
array[1,2] --一维数组
array[[1,2],[3,5]] --二维数组
'{99,889}'
不合理的:
array[[1,2],[3]] --元素长度不一致
array[[1,2],['Kenyon','good']] --类型不匹配
附数组操作符:
Operator Description Example Result =equalARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]t<>not equalARRAY[1,2,3] <> ARRAY[1,2,4]t<less thanARRAY[1,2,3] < ARRAY[1,2,4]t>greater thanARRAY[1,4,3] > ARRAY[1,2,4]t<=less than or equalARRAY[1,2,3] <= ARRAY[1,2,3]t>=greater than or equalARRAY[1,4,3] >= ARRAY[1,4,3]t@>containsARRAY[1,4,3] @> ARRAY[3,1]t<@is contained byARRAY[2,7] <@ ARRAY[1,7,4,2,6]t&&overlap (have elements in common)ARRAY[1,4,3] && ARRAY[2,1]t||array-to-array concatenationARRAY[1,2,3] || ARRAY[4,5,6]{1,2,3,4,5,6}||array-to-array concatenationARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]{{1,2,3},{4,5,6},{7,8,9}}||element-to-array concatenation3 || ARRAY[4,5,6]{3,4,5,6}||array-to-element concatenationARRAY[4,5,6] || 7{4,5,6,7}
数组函数:
Function Return Type Description Example Result array_append(anyarray, anyelement)anyarrayappend an element to the end of an arrayarray_append(ARRAY[1,2], 3){1,2,3}array_cat(anyarray, anyarray)anyarrayconcatenate two arraysarray_cat(ARRAY[1,2,3], ARRAY[4,5]){1,2,3,4,5}array_ndims(anyarray)intreturns the number of dimensions of the arrayarray_ndims(ARRAY[[1,2,3], [4,5,6]])2array_dims(anyarray)textreturns a text representation of array's dimensionsarray_dims(ARRAY[[1,2,3], [4,5,6]])[1:2][1:3]array_fill(anyelement, int[], [, int[]])anyarrayreturns an array initialized with supplied value and dimensions, optionally with lower bounds other than 1array_fill(7, ARRAY[3], ARRAY[2])[2:4]={7,7,7}array_length(anyarray, int)intreturns the length of the requested array dimensionarray_length(array[1,2,3], 1)3array_lower(anyarray, int)intreturns lower bound of the requested array dimensionarray_lower('[0:2]={1,2,3}'::int[], 1)0array_prepend(anyelement, anyarray)anyarrayappend an element to the beginning of an arrayarray_prepend(1, ARRAY[2,3]){1,2,3}array_to_string(anyarray, text [, text])textconcatenates array elements using supplied delimiter and optional null stringarray_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*')1,2,3,*,5array_upper(anyarray, int)intreturns upper bound of the requested array dimensionarray_upper(ARRAY[1,8,3,7], 1)4string_to_array(text, text [, text])text[]splits string into array elements using supplied delimiter and optional null stringstring_to_array('xx~^~yy~^~zz', '~^~', 'yy'){xx,NULL,zz}unnest(anyarray)setof anyelementexpand an array to a set of rowsunnest(ARRAY[1,2])
参考:http://www.postgresql.org/docs/9.2/static/functions-array.html
环境:
OS:CentOS 6.2
DB: PostgreSQL 9.2.4
1.数组的定义
不一样的维度元素长度定义在数据库中的实际存储都是一样的,数组元素的长度和类型必须要保持一致,并且以中括号来表示。
合理的:
array[1,2] --一维数组
array[[1,2],[3,5]] --二维数组
'{99,889}'
不合理的:
array[[1,2],[3]] --元素长度不一致
array[[1,2],['Kenyon','good']] --类型不匹配
[postgres@localhost ~]$ psqlpsql (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 TABLEpostgres=# \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: nopostgres=# 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 TABLEpostgres=# \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 1postgres=# insert into t_kenyon(items) values('{3,4,5}');INSERT 0 1postgres=# insert into t_kenyon(items) values(array[6,7,8,9]);INSERT 0 1postgres=# 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 1postgres=# delete from t_kenyon where items[1] = 4;DELETE 0postgres=# delete from t_kenyon where items[1] = 3;DELETE 1c.数据更新
往后追加postgres=# update t_kenyon set items = items||7;UPDATE 1postgres=# select * from t_kenyon;id | items ----+--------- 1 | {1,2,7}(1 row)postgres=# update t_kenyon set items = items||'{99,66}';UPDATE 1postgres=# 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 1postgres=# 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 1postgres=# 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 TABLEpostgres=# insert into t_kenyon values(1,'{1,2,3}');INSERT 0 1postgres=# insert into t_kenyon values(1,'{2,4}');INSERT 0 1postgres=# insert into t_kenyon values(1,'{34,7,8}');INSERT 0 1postgres=# insert into t_kenyon values(1,'{99,12}');INSERT 0 1postgres=# create index idx_t_kenyon on t_kenyon using gin(items);CREATE INDEXpostgres=# 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)
附数组操作符:
数组函数:
12(2 rows)
参考:http://www.postgresql.org/docs/9.2/static/functions-array.html
0 0
- PostgreSQL数组使用
- PostgreSQL: array 数组类型添加元素 数组的使用
- PostgreSQL 存储过程中回滚(ROLLBACK)和数组的使用
- PostGreSQL入门教程(三)- 数组的简单使用
- PostgreSQL使用
- postgresql使用
- PostgreSQL使用
- postgresql 使用
- postgreSQL 使用
- postgresql 数组问题
- postgresql 数组 (2)
- PostgreSQL 给数组排序
- postgresql 数组类型
- memo:PostgreSQL的数组函数
- PostgreSQL 对数组的遍历
- postgresql的查询函数(数组)
- PostgreSQL中的数组与Any
- postgresql将数组变为行
- Android中自定义对话框
- JavaEE中文件的下载和文件的上传(--------前后台交互----------)
- LoadRunner常遇见的问题
- 粗略应用setBoeder,JTree
- 利用easyBCD安装ubuntu双系统常见错误总结
- PostgreSQL数组使用
- 有符号数溢出的判断
- LoadRunner几个重要的概念:事务、集合点、思考时间
- cmake编译工程列子
- HDOJ 3665 Seaside【Dijkstra】【SPFA】【Floyd】
- 处理GitHub不允许上传大于100M文件问题
- 安一个家在这里。
- LoadRunner性能测试-思考时间
- 王垠的过去和现状