关闭

postgres 数组

标签: postgres
3258人阅读 评论(0) 收藏 举报
分类:

1.来自:http://pgguide.lxneng.com/sexy/arrays.html

Postgres 允许把字段定义为可变长度的数组. 数据的类型可以是内置的类型, 用户自定义的类型或者枚举类型.


在创建表时声明数组字段:


CREATE TABLE rock_band
(
   name text,
   members text[]
)
上面的语句将创建一张 rock_band 表, 它定义了一个text类型的字段 name 来表示乐队的名称, 还有一个 members 字段以二维数组的类型来保存队员的名字.


插入数组值
INSERT INTO rock_band
VALUES
('Led Zeppelin',
'{"Page", "Plant", "Jones", "Bonham"}'
)
注意: 数组元素值是用双引号引起来的, 如果是单引号就会出错的.


查询出来将是这样的:


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)
要查找数组中所有值都匹配某个值, 可以使用ALL.


2.

来自:http://www.postgresql.org/docs/9.2/static/functions-array.html

9.18. Array Functions and Operators

Table 9-41 shows the operators available for array types.

Table 9-41. Array Operators

Operator Description Example Result
= equal ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] t
<> not equal ARRAY[1,2,3] <> ARRAY[1,2,4] t
< less than ARRAY[1,2,3] < ARRAY[1,2,4] t
> greater than ARRAY[1,4,3] > ARRAY[1,2,4] t
<= less than or equal ARRAY[1,2,3] <= ARRAY[1,2,3] t
>= greater than or equal ARRAY[1,4,3] >= ARRAY[1,4,3] t
@> contains ARRAY[1,4,3] @> ARRAY[3,1] t
<@ is contained by ARRAY[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 concatenation ARRAY[1,2,3] || ARRAY[4,5,6] {1,2,3,4,5,6}
|| array-to-array concatenation ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] {{1,2,3},{4,5,6},{7,8,9}}
|| element-to-array concatenation 3 || ARRAY[4,5,6] {3,4,5,6}
|| array-to-element concatenation ARRAY[4,5,6] || 7 {4,5,6,7}

Array comparisons compare the array contents element-by-element, using the default B-tree comparison function for the element data type. In multidimensional arrays the elements are visited in row-major order (last subscript varies most rapidly). If the contents of two arrays are equal but the dimensionality is different, the first difference in the dimensionality information determines the sort order. (This is a change from versions of PostgreSQL prior to 8.2: older versions would claim that two arrays with the same contents were equal, even if the number of dimensions or subscript ranges were different.)

See Section 8.15 for more details about array operator behavior.

Table 9-42 shows the functions available for use with array types. See Section 8.15 for more information and examples of the use of these functions.

Table 9-42. Array Functions

Function Return Type Description Example Result
array_append(anyarray,anyelement) anyarray append an element to the end of an array array_append(ARRAY[1,2], 3) {1,2,3}
array_cat(anyarrayanyarray) anyarray concatenate two arrays array_cat(ARRAY[1,2,3], ARRAY[4,5]) {1,2,3,4,5}
array_ndims(anyarray) int returns the number of dimensions of the array array_ndims(ARRAY[[1,2,3], [4,5,6]]) 2
array_dims(anyarray) text returns a text representation of array's dimensions array_dims(ARRAY[[1,2,3], [4,5,6]]) [1:2][1:3]
array_fill(anyelementint[], [,int[]]) anyarray 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,7}
array_length(anyarrayint) int returns the length of the requested array dimension array_length(array[1,2,3], 1) 3
array_lower(anyarrayint) int returns lower bound of the requested array dimension array_lower('[0:2]={1,2,3}'::int[], 1) 0
array_prepend(anyelement,anyarray) anyarray append an element to the beginning of an array array_prepend(1, ARRAY[2,3]) {1,2,3}
array_to_string(anyarraytext[text]) text concatenates array elements using supplied delimiter and optional null string array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') 1,2,3,*,5
array_upper(anyarrayint) int returns upper bound of the requested array dimension array_upper(ARRAY[1,8,3,7], 1) 4
string_to_array(texttext [,text]) text[] splits string into array elements using supplied delimiter and optional null string string_to_array('xx~^~yy~^~zz', '~^~', 'yy') {xx,NULL,zz}
unnest(anyarray) setof anyelement expand an array to a set of rows unnest(ARRAY[1,2])
1
2
(2 rows)

In string_to_array, if the delimiter parameter is NULL, each character in the input string will become a separate element in the resulting array. If the delimiter is an empty string, then the entire input string is returned as a one-element array. Otherwise the input string is split at each occurrence of the delimiter string.

In string_to_array, if the null-string parameter is omitted or NULL, none of the substrings of the input will be replaced by NULL. In array_to_string, if the null-string parameter is omitted or NULL, any null elements in the array are simply skipped and not represented in the output string.

Note: There are two differences in the behavior of string_to_array from pre-9.1 versions ofPostgreSQL. First, it will return an empty (zero-element) array rather than NULL when the input string is of zero length. Second, if the delimiter string is NULL, the function splits the input into individual characters, rather than returning NULL as before.

See also Section 9.20 about the aggregate function array_agg for use with arrays.

3.

来自:http://my.oschina.net/Kenyon/blog/133974

开发的语言有数组的概念,对应于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']]  --类型不匹配

[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)

附数组操作符: 
Operator Description Example Result
= equal ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] t
<> not equal ARRAY[1,2,3] <> ARRAY[1,2,4] t
< less than ARRAY[1,2,3] < ARRAY[1,2,4] t
> greater than ARRAY[1,4,3] > ARRAY[1,2,4] t
<= less than or equal ARRAY[1,2,3] <= ARRAY[1,2,3] t
>= greater than or equal ARRAY[1,4,3] >= ARRAY[1,4,3] t
@> contains ARRAY[1,4,3] @> ARRAY[3,1] t
<@ is contained by ARRAY[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 concatenation ARRAY[1,2,3] || ARRAY[4,5,6] {1,2,3,4,5,6}
|| array-to-array concatenation ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] {{1,2,3},{4,5,6},{7,8,9}}
|| element-to-array concatenation 3 || ARRAY[4,5,6] {3,4,5,6}
|| array-to-element concatenation ARRAY[4,5,6] || 7 {4,5,6,7}


数组函数: 
Function Return Type Description Example Result
array_append(anyarray, anyelement) anyarray append an element to the end of an array array_append(ARRAY[1,2], 3) {1,2,3}
array_cat(anyarray, anyarray) anyarray concatenate two arrays array_cat(ARRAY[1,2,3], ARRAY[4,5]) {1,2,3,4,5}
array_ndims(anyarray) int returns the number of dimensions of the array array_ndims(ARRAY[[1,2,3], [4,5,6]]) 2
array_dims(anyarray) text returns a text representation of array's dimensions array_dims(ARRAY[[1,2,3], [4,5,6]]) [1:2][1:3]
array_fill(anyelement, int[], [, int[]]) anyarray 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,7}
array_length(anyarray, int) int returns the length of the requested array dimension array_length(array[1,2,3], 1) 3
array_lower(anyarray, int) int returns lower bound of the requested array dimension array_lower('[0:2]={1,2,3}'::int[], 1) 0
array_prepend(anyelement, anyarray) anyarray append an element to the beginning of an array array_prepend(1, ARRAY[2,3]) {1,2,3}
array_to_string(anyarray, text [, text]) text concatenates array elements using supplied delimiter and optional null string array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') 1,2,3,*,5
array_upper(anyarray, int) int returns upper bound of the requested array dimension array_upper(ARRAY[1,8,3,7], 1) 4
string_to_array(text, text [, text]) text[] splits string into array elements using supplied delimiter and optional null string string_to_array('xx~^~yy~^~zz', '~^~', 'yy') {xx,NULL,zz}
unnest(anyarray) setof anyelement expand an array to a set of rows unnest(ARRAY[1,2])
1
2
(2 rows)



0
0
查看评论

postgresql 存储过程时 如何把查到数据放到数组中,计算出数组中数字个数字

postgresql 存储过程时 如何把查到数据放到数组中,计算出数组中数字个数字
  • IT_ziliang
  • IT_ziliang
  • 2015-10-29 18:47
  • 1913

PostgreSQL数组使用

开发的语言有数组的概念,对应于postgresql也有相关的数据字段类型,数组是英文array的翻译,可以定义一维,二维甚至更多维度,数学上跟矩阵很类似。在postgres里面可以直接存储使用,某些场景下使用很方便,也很强大。  环境:  OS:CentOS 6.2...
  • luckypeng
  • luckypeng
  • 2015-11-12 19:37
  • 5282

PostgreSQL学习笔记7之函数和操作符<三>

九、序列操作函数:     序列对象(也叫序列生成器)都是用CREATE SEQUENCE创建的特殊的单行表。一个序列对象通常用于为行或者表生成唯一的标识符。下面序列函数,为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法。 函数 ...
  • shihuacai
  • shihuacai
  • 2013-01-31 22:27
  • 897

PostgreSQL 对数组的遍历

PostgreSQL 对数组的遍历。
  • yueliangdao0608
  • yueliangdao0608
  • 2014-01-24 15:27
  • 10151

postgresql 数据支持 jsonb/json中 array或int 类型进行的交集比较

1.应用场景 如果一个表格中含有 一个 jsonb 的字段类型 : 字段中有一个 array 类型的数据 或 number 类型的数据。(注意这个类型是用jsonb_typeof( parent -> children) 方法 显示出来的) postgresql 数据支持 jso...
  • Felix_Dreammaker
  • Felix_Dreammaker
  • 2017-11-11 18:16
  • 251

postgresql将数组变为行

有的时候需要把数组元素同表中的字段进行关系运算,首先得把array变为记录行SELECT "unnest"(array[1,2,3])结果: unnest 1 2 3求数组交集:SELECT "unnest"(array[1,2,3]) INTERSECT S...
  • NorthWood
  • NorthWood
  • 2016-02-01 15:40
  • 436

PostgreSQL总结(2)特殊数据类型查询 - array

我们知道,对于数据库的大部分操作都是查询操作,PostgreSQL具有丰富的数据类型,有一些特殊的数据类型操作与其他关系型数据库有所区别,这里我们来介绍一下对于这些数据类型的查询。
  • cqdzdj
  • cqdzdj
  • 2016-08-29 18:06
  • 1442

memo:PostgreSQL的数组函数

数组也是我们SQL中经常使用的类型。像unnest这个函数可以把数组转换成一个子查询,可以方便地实现一些功能。利用好数组有时候会提高系统性能。 比如减少JDBC执行次数,使用数组后能够使得查询条件使用索引等。 Table 9-40. Array Operators Op...
  • hantiannan
  • hantiannan
  • 2012-05-15 12:13
  • 4483

【数据库】postgresql小函数---将字符串split成为数组

declare _strres varchar(800); declare _originStr varchar(100); declare _cindex INTEGER; declare _delimeter VARCHAR(10); declare _arrIndex INTEGER; DEC...
  • cdnight
  • cdnight
  • 2014-01-09 18:00
  • 7495

PostgreSQL中的数组与Any

建立表: CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][] ); 插入数据: INSERT INTO sal_emp VALU...
  • luckypeng
  • luckypeng
  • 2015-11-08 00:14
  • 945
    个人资料
    • 访问:521548次
    • 积分:6677
    • 等级:
    • 排名:第4238名
    • 原创:147篇
    • 转载:181篇
    • 译文:2篇
    • 评论:14条
    个人介绍
    晨风.

    E-mail: wangln@sina.cn

    博客专栏
    文章分类