PostgreSQL数组使用

Postgre SQL 前端之家
前端之家收集整理的这篇文章主要介绍了PostgreSQL数组使用，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
开发的语言有数组的概念，对应于postgresql也有相关的数据字段类型，数组是英文array的翻译，可以定义一维，二维甚至更多维度，数学上跟矩阵很类似。在postgres里面可以直接存储使用，某些场景下使用很方便，也很强大。

环境：
OS：CentOS 6.2
DB: Postgresql 9.2.4
1.数组的定义
不一样的维度元素长度定义在数据库中的实际存储都是一样的，数组元素的长度和类型必须要保持一致，并且以中括号来表示。
合理的：
array[1,2] --一维数组
array[[1,2],[3,5]] --二维数组
'{99,889}'
不合理的: 
  1. [postgres@localhost ~]$ psql
  2. psql (9.2.4)
  3. Type "help" for help.
  4. postgres=# create table t_kenyon(id serial primary key,items int[]);
  5. NOTICE:  CREATE TABLE will create implicit sequence "t_kenyon_id_seq" for serial column "t_kenyon.id"
  6. NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_kenyon_pkey" for table "t_kenyon"
  7. CREATE TABLE
  8. postgres=# \d+ t_kenyon
  9.                                               Table "public.t_kenyon"
  10. Column |   Type    |                       Modifiers                       | Storage  | Stats target | Description
  11. --------+-----------+-------------------------------------------------------+----------+--------------+-------------
  12. id     | integer   | not null default nextval('t_kenyon_id_seq'::regclass) | plain    |              |
  13. items  | integer[] |                                                       | extended |              |
  14. Indexes:
  15.     "t_kenyon_pkey" PRIMARY KEY,btree (id)
  16. Has OIDs: no
  17.  
  18. postgres=# create table t_ken(id serial primary key,items int[4]);
  19. NOTICE:  CREATE TABLE will create implicit sequence "t_ken_id_seq" for serial column "t_ken.id"
  20. NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_ken_pkey" for table "t_ken"
  21. CREATE TABLE
  22.  
  23. postgres=# \d+ t_ken
  24.                        Table "public.t_ken"
  25. Column |  Type  |           Modifiers           | Storage | Stats target | Description
  26. --------+-----------+----------------------------------------------------+----------+--------------+-------------
  27. id   | integer  | not null default nextval('t_ken_id_seq'::regclass) | plain  |       |
  28. items | integer[] |                          | extended |       |
  29. Indexes:
  30.   "t_ken_pkey" PRIMARY KEY,btree (id)
  31. Has OIDs: no
  32.  
  33. 数组的存储方式是extended的。
2.数组操作 
  1. a.数据插入，有两种方式
  2. postgres=# insert into t_kenyon(items) values('{1,2}');
  3. INSERT 0 1
  4. postgres=# insert into t_kenyon(items) values('{3,4,5}');
  5. INSERT 0 1
  6. postgres=# insert into t_kenyon(items) values(array[6,7,8,9]);
  7. INSERT 0 1
  8. postgres=# select * from t_kenyon;
  9. id |   items  
  10. ----+-----------
  11.   1 | {1,2}
  12.   2 | {3,5}
  13.   3 | {6,9}
  14. (3 rows)
b.数据删除 
  1. postgres=# delete from t_kenyon where id = 3;
  2. DELETE 1
  3. postgres=# delete from t_kenyon where items[1] = 4;
  4. DELETE 0
  5. postgres=# delete from t_kenyon where items[1] = 3;
  6. DELETE 1
c.数据更新 
  1. 往后追加
  2. postgres=# update t_kenyon set items = items||7;
  3. UPDATE 1
  4. postgres=# select * from t_kenyon;
  5. id |  items 
  6. ----+---------
  7.   1 | {1,2,7}
  8. (1 row)
  9.  
  10. postgres=# update t_kenyon set items = items||'{99,66}';
  11. UPDATE 1
  12. postgres=# select * from t_kenyon;
  13. id |      items      
  14. ----+------------------
  15.   1 | {1,55,99,66}
  16. (1 row)
  17.  
  18. 往前插
  19. postgres=# update t_kenyon set items = array_prepend(55,items) ;
  20. UPDATE 1
  21. postgres=# select * from t_kenyon;
  22. id |        items       
  23. ----+---------------------
  24.   1 | {55,1,66}
  25. (1 row)
d.数据查询 
  1. postgres=# insert into t_kenyon(items) values('{3,5}');
  2. INSERT 0 1
  3.  
  4. postgres=# select * from t_kenyon where id = 1;
  5. id |        items       
  6. ----+---------------------
  7.   1 | {55,66}
  8. (1 row)
  9.  
  10. postgres=# select * from t_kenyon where items[1] = 55;
  11. id |        items       
  12. ----+---------------------
  13.   1 | {55,66}
  14. (1 row)
  15.  
  16. postgres=# select * from t_kenyon where items[3] = 5;
  17. id |  items 
  18. ----+---------
  19.   4 | {3,5}
  20. (1 row)
  21.  
  22. postgres=# select items[1],items[3],items[4] from t_kenyon;
  23. items | items | items
  24. -------+-------+-------
  25.     55 |     2 |     7
  26.      3 |     5 |     
  27. (2 rows)
  28.  
  29. postgres=# select unnest(items) from t_kenyon where id = 4;
  30. unnest
  31. --------
  32.       3
  33.       4
  34.       5
  35. (3 rows)
e.数组比较 
  1. postgres=# select ARRAY[1,3] <= ARRAY[1,3];
  2. ?column?
  3. ----------
  4. t
  5. (1 row)
f.数组字段类型转换 
  1. postgres=# select array[['11','12'],['23','34']]::int[];
  2.        array      
  3. -------------------
  4. {{11,12},{23,34}}
  5. (1 row)
  6.  
  7. postgres=# select array[[11,12],[23,34]]::text[];
  8.        array      
  9. -------------------
  10. {{11,34}}
  11. (1 row)
3.数组索引 postgres=# create table t_kenyon(id int,items int[]); CREATE TABLE postgres=# insert into t_kenyon values(1,'{1,3}'); INSERT 0 1 postgres=# insert into t_kenyon values(1,'{2,4}'); INSERT 0 1 postgres=# insert into t_kenyon values(1,'{34,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)
附数组操作符:
Operator Description Example Result
= equal ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,3] t
<> not equal ARRAY[1,3] <> ARRAY[1,4] < less than > greater than <= less than or equal >= greater than or equal @> contains <@ is contained by ARRAY[2,7] <@ ARRAY[1,6] && overlap (have elements in common) || array-to-array concatenation ARRAY[1,3] || ARRAY[4,5,239)"> {1,3,6}
|| array-to-array concatenation {{1,3},{4,6},{7,9}}
element-to-array concatenation 3 || ARRAY[4,248)">{3,248)">array-to-element concatenation ARRAY[4,6] || 7 {4,6,7}

数组函数：
Function Return Type array_append(anyarray,anyelement) anyarray append an element to the end of an array array_append(ARRAY[1,3) {1,3} array_cat(anyarray,anyarray) anyarray concatenate two arrays array_cat(ARRAY[1,3],ARRAY[4,5]) array_ndims(anyarray) int returns the number of dimensions of the array array_ndims(ARRAY[[1,[4,6]]) 2 array_dims(anyarray) text returns a text representation of array's dimensions array_dims(ARRAY[[1,248)">[1:2][1:3] array_fill(anyelement,int[],[,int[]]) returns an array initialized with supplied value and dimensions,optionally with lower bounds other than 1 array_fill(7,ARRAY[3],ARRAY[2]) [2:4]={7,7} array_length(anyarray,int) returns the length of the requested array dimension array_length(array[1,1) 3 array_lower(anyarray,248)">returns lower bound of the requested array dimension array_lower('[0:2]={1,3}'::int[],248)">0 array_prepend(anyelement,248)">append an element to the beginning of an array array_prepend(1,ARRAY[2,3]) array_to_string(anyarray,text[,text]) concatenates array elements using supplied delimiter and optional null string array_to_string(ARRAY[1,NULL,5],','*') 1,*,5 array_upper(anyarray,248)">returns upper bound of the requested array dimension array_upper(ARRAY[1,7],248)">4 string_to_array(text,248)">text[] splits string into array elements using supplied delimiter and optional null string string_to_array('xx~^~yy~^~zz','~^~','yy') {xx,zz} unnest(anyarray) setof anyelement expand an array to a set of rows unnest(ARRAY[1,2]) 
  1. 1
  2. 2
(2 rows)
参考：http://www.postgresql.org/docs/9.2/static/functions-array.html

猜你在找的Postgre SQL相关文章

PosegreSQL基础回顾(第 4 章 SQL语法)
来源：http://www.postgres.cn/docs/11/ 4.1.1.&#160;标识符和关键词 SQL标识符和关键...
PosegreSQL基础回顾(第 8 章 数据类型)
来源：http://www.postgres.cn/docs/11/ 8.1.&#160;数字类型 数字类型由2、4或8字节的...
PosegreSQL基础回顾(第 5 章 数据定义)
来源：http://www.postgres.cn/docs/11/ 5.1.&#160;表基础 SQL并不保证表中行的顺序。...
PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
来源：http://www.postgres.cn/docs/11/ 6.4.&#160;从修改的行中返回数据 有时在修改行...
PosegreSQL基础回顾(第 13 章 并发控制)
来源：http://www.postgres.cn/docs/11/ 13.2.1.&#160;读已提交隔离级别 读已提交是Po...
PostgreSQL安装和使用
青岛OJ系统用的关系型数据库是PostgreSQL,为此对PostgreSQL大致了解下。 今天的主要话题围...
PostgreSQL整体架构
 
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
专访唐成：从小工到专家，PostgreSQL的修炼之道
唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问