文档章节

PostgreSQL数组使用

kenyon_君羊
 kenyon_君羊
发布于 2013/05/28 17:03
字数 1137
阅读 13237
收藏 21
点赞 0
评论 1
开发的语言有数组的概念,对应于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)

 
参考:http://www.postgresql.org/docs/9.2/static/functions-array.html

© 著作权归作者所有

共有 人打赏支持
kenyon_君羊
粉丝 495
博文 170
码字总数 121714
作品 0
杭州
其他
加载中

评论(1)

猫咪要感冒
猫咪要感冒
set enable_seqscan = off;
强制走索引?
PostgreSQL Oracle PL/SQL 兼容性之 - AGGREGATE USING Clause 聚合函数

标签 PostgreSQL , Oracle , PL/SQL , 聚合函数 , 自定义聚合函数 背景 Oracle的自定义聚合函数的定义方法,在创建函数是,使用AGGREGATE USING Clause关键词。 AGGREGATE USING Clause Spe...

德哥 ⋅ 06/21 ⋅ 0

PostgreSQL Oracle 兼容性之 - 自定义并行聚合函数 PARALLEL_ENABLE AGGREGATE

标签 PostgreSQL , Oracle , 自定义聚合 , 并行 背景 Oracle,PostgreSQL均支持自定义聚合函数,例如PostgreSQL自定义聚合函数的语法如下: Oracle的语法与之有所不同,并且支持并行。 Post...

德哥 ⋅ 04/18 ⋅ 0

PostgreSQL 类微博FEED系统 - 设计与性能指标

标签 PostgreSQL , feed , 微博 , 推送 , 分区 , 分片 , UDF , 挖掘 , 文本挖掘 背景 类微博系统,最频繁用到的功能: 之前写过一篇《三体高可用PCC大赛 - facebook微博 like场景 - 数据库设...

德哥 ⋅ 04/18 ⋅ 0

PostgreSQL 相似文本检索与去重 - (银屑病怎么治?银屑病怎么治疗?银屑病怎么治疗好?银屑病怎么能治疗好?)

标签 PostgreSQL , 相似字符串 , 全文检索 , 去重 , 相似问题 , 医疗 , plr , plpython , madlib , 文本处理 背景 在云栖社区的问答区,有一位网友提到有一个问题: 解这个问题的思路 1. 首先...

德哥 ⋅ 04/18 ⋅ 0

数据库案例集锦 - 开发者的《如来神掌》

标签 PostgreSQL , PG DBA cookbook , PG Oracle兼容性 , PG 架构师 cookbook , PG 开发者 cookbook , PG 应用案例 背景 「剑魔独孤求败,纵横江湖三十馀载,杀尽仇寇,败尽英雄,天下更无抗...

德哥 ⋅ 2017/06/09 ⋅ 0

Oracle DBA 增值 PostgreSQL,Greenplum 学习计划

标签 PostgreSQL , Oracle , Greenplum 背景 去O很大程度上是国家层面的战略考虑,比如斯诺登事件,最近贸易战的“中兴”事件,使得去O成为一个不可不做的事情。 但是去O喊了若干年,并没有真...

德哥 ⋅ 05/06 ⋅ 0

PostgreSQL 11 preview - 多阶段并行聚合array_agg, string_agg

标签 PostgreSQL , stringagg , arrayagg 背景 并行聚合原理请参考: 《PostgreSQL 10 自定义并行计算聚合函数的原理与实践 - (含arrayagg合并多个数组为单个一元数组的例子)》 实际上Postg...

德哥 ⋅ 04/18 ⋅ 0

PostgreSQL 多值类型(如数组),元素值全局唯一 - 约束如何实现

标签 PostgreSQL , 多值类型 , 数组 , 全局约束 背景 《PostgreSQL 11 preview - 支持 数组外键约束》 在PostgreSQL中,应用可以非常方便使用多值类型,比如数组、全文检索、范围类型等。 对...

德哥 ⋅ 04/18 ⋅ 0

PostgreSQL Oracle 兼容性之 - create type

标签 PostgreSQL , Oracle , create type , method , constructor , table type , composite type , domain , enum , range , operator , index , udf 背景 Oracle 自定义类型与PostgreSQL自......

德哥 ⋅ 05/06 ⋅ 0

DevOps 资讯 | PostgreSQL 的时代到来了吗 ?

PostgreSQL是对象-关系型数据库,BSD 许可证。拼读为"post-gress-Q-L"。 作者: Tony Baer 原文: Has the time finally come for PostgreSQL?(有删节) 近30年来 PostgreSQL 无疑是您从未听...

RiboseYim ⋅ 前天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Linux中的端口大全

1 被LANA定义的端口 端口 名称 描述 1 tcpmux TCP 端口服务多路复用 5 rje 远程作业入口 7 echo Echo 服务 9 discard 用于连接测试的空服务 11 systat 用于列举连接了的端口的系统状态 13 d...

寰宇01 ⋅ 10分钟前 ⋅ 0

Confluence 6 如何备份存储文件和页面信息

备份的 ZIP 文件包含有 entities.xml,这个 XML 文件包含有 Confluence 的所有页面内容和存储附件的目录。 备份 Zip 文件结构 页面的附件是存储在附件存储目录中的,通过页面和附件 ID 进行识...

honeymose ⋅ 12分钟前 ⋅ 0

【每天一个JQuery特效】根据状态确定是否滑入或滑出被选元素

主要效果: 本文主要采用slideToggle()方法实现以一行代码同时实现以展开或收缩的方式显示或隐藏被选元素。 主要代码如下: <!DOCTYPE html><html><head><meta charset="UTF-8">...

Rhymo-Wu ⋅ 16分钟前 ⋅ 0

度量.net framework 迁移到.net core的工作量

把现有的.net framework程序迁移到.net core上,是一个非常复杂的工作,特别是一些API在两个平台上还不能同时支持。两个类库的差异性,通过人工很难识别全。好在微软的工程师们考虑到了我们顾...

李朝强 ⋅ 21分钟前 ⋅ 0

请不要在“微服务”的狂热中迷失自我!

微服务在过去几年一直是一个非常热门的话题(附录1)。何为“微服务的疯狂”,举个例子: 众所周知,Netflix在DevOps上的表现非常棒。Netfix可以做微服务。因此:如果我做微服务,我也将非常...

harries ⋅ 23分钟前 ⋅ 0

oAuth2 升级Spring Cloud Finchley.RELEASE踩坑分享

背景 6.19号,spring团队发布了期待已久的 Spring Cloud Finchley.RELEASE 版本。 重要变化: 基于Spring Boot 2.0.X 不兼容 Spring Boot 1.5.X 期间踩过几个坑,分享出来给大伙,主要是关于...

冷冷gg ⋅ 53分钟前 ⋅ 0

OSChina 周一乱弹 —— 理发师小姐姐的魔法

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @冰冰棒- :分享田馥甄的单曲《My Love》 《My Love》- 田馥甄 手机党少年们想听歌,请使劲儿戳(这里) @Li-Wang :哎,头发又长了。。。又要...

小小编辑 ⋅ 今天 ⋅ 8

Kafka1.0.X_消费者API详解2

偏移量由消费者管理 kafka Consumer Api还提供了自己存储offset的功能,将offset和data做到原子性,可以让消费具有Exactly Once 的语义,比kafka默认的At-least Once更强大 消费者从指定分区...

特拉仔 ⋅ 今天 ⋅ 0

NEO智能合约之发布和升级(二)

接NEO智能合约之发布和升级(一),我们接下来说说智能合约的升级功能。 一 准备工作 合约的升级需要在合约内预先设置好升级接口,以方便在升级时调用。接下来我们对NEO智能合约之发布和升级...

红烧飞鱼 ⋅ 今天 ⋅ 0

个人博客的运营模式能否学习TMALL天猫质量为上?

心情随笔|个人博客的运营模式能否学习TMALL天猫质量为上? 中国的互联网已经发展了很多年了,记得在十年前,个人博客十分流行,大量的人都在写博客,而且质量还不错,很多高质量的文章都是在...

原创小博客 ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部