PostgreSQL数组类型的增、删、改、查详解

我会带着你远行 2024-03-24 12:27 50阅读 0赞

#### 文章目录 ####

*  前言
 *  一、定义数组类型
 *  二、数组类型插入数据
 *  三、数组类型修改数据
 *  四、数组类型删除数据
 *  五、数组类型查询数据
 *  六、数组类型操作符
 *  七、数组类型常用函数

--------------------

## 前言 ##

PostgreSQL支持表的字段使用定长或可变长度的一维或多维数组，数组的类型可以是任何数据库内建的类型、用户自定义的类型、枚举类型以及组合类型。  
数组类型的定义就是通过在其基本类型后面附加中括号 \[\] 来实现的，中括号中可以给一个长度数字，也可以不给。

## 一、定义数组类型 ##

首先我们先创建了t\_test表，里面包含int类型的数组字段name和text类型的数组字段address，如下的代码片段。

create table t_test (
    	id bigint,
    	name integer[],
    	address text[]
    );

## 二、数组类型插入数据 ##

**方式1：使用\{\}将数据转为数组**

insert into t_test(id,name,address) values(1,'{1,2}','{"beijing","shanghai"}');

**方式2：使用array将数据转为数组**

insert into t_test(id,name,address) values(2,array[1,2],array['beijing','shanghai']);

插入后的结果如下图。  
![在这里插入图片描述][9c24c610df6841278c5ff6fc7ccbca37.png]

## 三、数组类型修改数据 ##

尾部追加数组元素：可以使用array\_append函数或者使用||。

--使用||修改
    update t_test set name = name||'{3}' , address = address||'{"shenzhen"}' where id=1;
    --使用array_append函数修改
    update t_test set name = array_append(name,3) , address = array_append(address,'shenzhen') where id=1;

头部追加数组元素：使用array\_prepend函数

update t_test set name = array_prepend(3,name) , address = array_prepend('shenzhen',address) where id=2;

## 四、数组类型删除数据 ##

通过数组类型删除整行数据。

--如果name数组的第二个元素为1，则删除整行数据
    delete from t_test where name[2] = 1;

如果要删除数组中的某个元素，需要利用array\_remove函数配合update语句实现，代码如下。

update t_test set name= array_remove(name,3) where id=2;

## 五、数组类型查询数据 ##

查询数组中某个元素，例如查询address数组及address数组的第二个元素

select address,address[2] from t_test;

查询name数组中包含3元素的数据行

select * from t_test where array_position(name,3)>0;

## 六、数组类型操作符 ##

以下操作符是用于数组间的处理或者判断。

<table> 
 <thead> 
  <tr> 
   <th>操作符</th> 
   <th>描述</th> 
   <th>例子</th> 
   <th>结果</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>=</td> 
   <td>等于</td> 
   <td>ARRAY[1,2,3] = ARRAY[1,2,3]</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>&lt;&gt;</td> 
   <td>不等于</td> 
   <td>ARRAY[1,2,3] &lt;&gt; ARRAY[1,2,4]</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>&lt;</td> 
   <td>小于</td> 
   <td>ARRAY[1,2,3] &lt; ARRAY[1,2,4]</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>&gt;</td> 
   <td>大于</td> 
   <td>ARRAY[1,4,3] &gt; ARRAY[1,2,4]</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>小于或等于</td> 
   <td>ARRAY[1,2,3] &lt;= ARRAY[1,2,3]</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>&gt;=</td> 
   <td>大于或等于</td> 
   <td>ARRAY[1,4,3] &gt;= ARRAY[1,4,3]</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>@&gt;</td> 
   <td>包含</td> 
   <td>ARRAY[1,4,3] @&gt; ARRAY[3,1]</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>&lt;@</td> 
   <td>被包含于</td> 
   <td>ARRAY[2,7] &lt;@ ARRAY[1,7,4,2,6]</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>&amp;&amp;</td> 
   <td>重叠(有共同元素)</td> 
   <td>ARRAY[1,4,3] &amp;&amp; ARRAY[2,1]</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>||</td> 
   <td>数组与数组连接</td> 
   <td>ARRAY[1,2,3] || ARRAY[4,5,6]</td> 
   <td>{1,2,3,4,5,6}</td> 
  </tr> 
 </tbody> 
</table>

## 七、数组类型常用函数 ##

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
   <th>语句</th> 
   <th>结果</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>array_append</td> 
   <td>向数组的末尾添加元素</td> 
   <td>array_append(ARRAY[1,2], 3)</td> 
   <td>{1,2,3}</td> 
  </tr> 
  <tr> 
   <td>array_prepend</td> 
   <td>向数组的开头添加函数</td> 
   <td>array_prepend(1, ARRAY[2,3])</td> 
   <td>{1,2,3}</td> 
  </tr> 
  <tr> 
   <td>array_cat</td> 
   <td>连接两个数组</td> 
   <td>array_cat(ARRAY[1,2,3], ARRAY[4,5])</td> 
   <td>{1,2,3,4,5}</td> 
  </tr> 
  <tr> 
   <td>array_replace</td> 
   <td>用新值替换每个等于给定值的数组元素</td> 
   <td>array_replace(ARRAY[1,2,5,4], 5, 3)</td> 
   <td>{1,2,3,4}</td> 
  </tr> 
  <tr> 
   <td>array_remove</td> 
   <td>删除某个给定值的数组元素</td> 
   <td>array_remove(ARRAY[1,2,3,4],4)</td> 
   <td>{1,2,3}</td> 
  </tr> 
  <tr> 
   <td>[start:end]</td> 
   <td>通过下标范围数组元素</td> 
   <td>select (ARRAY[‘a’,‘b’,‘c’,‘d’])[1:2]</td> 
   <td>{a,b}</td> 
  </tr> 
  <tr> 
   <td>array_position</td> 
   <td>数组中指定元素出现的位置</td> 
   <td>array_position(ARRAY[1,8,3,7], 8)</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>array_dims</td> 
   <td>返回数组维数的文本表示</td> 
   <td>array_dims(ARRAY[[1,2,3], [4,5,6]])</td> 
   <td>[1:2][1:3]</td> 
  </tr> 
  <tr> 
   <td>array_lower</td> 
   <td>返回数组维数的下界</td> 
   <td>array_lower(ARRAY[1,2,3,4], 1)</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>array_upper</td> 
   <td>返回数组维数的上界</td> 
   <td>array_upper(ARRAY[1,2,3,4], 1)</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>array_ndims</td> 
   <td>返回数组的维数</td> 
   <td>array_ndims(ARRAY[[1,2,3], [4,5,6]])</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>array_length</td> 
   <td>返回数组维度的长度</td> 
   <td>array_length(array[1,2,3], 1)</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>cardinality</td> 
   <td>返回数组中的总元素数量，如果数组是空的则为0</td> 
   <td>cardinality(ARRAY[1,2,3,5])</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>array_to_string</td> 
   <td>将数组转换为字符串，使用分隔符连接数组元素</td> 
   <td>select array_to_string(ARRAY[1, 2, 3], ‘,’)</td> 
   <td>1,2,3</td> 
  </tr> 
  <tr> 
   <td>string_to_array</td> 
   <td>使用指定的分隔符把字符串分隔成数组元素</td> 
   <td>select string_to_array(‘a,b,c’,‘,’)</td> 
   <td>{a,b,c}</td> 
  </tr> 
  <tr> 
   <td>array_agg</td> 
   <td>把多个值合并到一个数组中</td> 
   <td>SELECT id, array_agg(label) FROM t_label group by id</td> 
   <td>{label1,label2}</td> 
  </tr> 
  <tr> 
   <td>unnest</td> 
   <td>分解一个数组为一组行</td> 
   <td>select unnest(ARRAY[1,2,3])</td> 
   <td>1<br>2<br>3</td> 
  </tr> 
 </tbody> 
</table>

[9c24c610df6841278c5ff6fc7ccbca37.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/ca82c043d7084722bd838b9c35cf9135.png