PostgreSQL数组类型应用

在使用 awk 脚本；数组是一大利器；在很多场景是用数组能处理。

在 python 中，数据类型list；相当于array类型。

在 Oracle 中，对 array 不够友好，感觉像是鸡肋。但是在 PostgreSQL 中，对array有很多支持，很多场景可以应用到。下面慢慢说

1|01、any(array) 替换 in（table）

-- 案例1
-- 创建表A；插入1000条记录；并每条记录重复4次
postgres=# create table A (id int, info text);
CREATE TABLE
postgres=# 
postgres=# insert into A select generate_series(1,1000), 'lottu';
INSERT 0 1000
postgres=# 
postgres=# insert into A select generate_series(1,1000), 'lottu';
INSERT 0 1000
postgres=# insert into A select * from A;
INSERT 0 2000
-- 用in的方式去处理重复数据
postgres=# begin;
BEGIN
postgres=# explain (analyze, costs, timing) delete from A where ctid not in (select min(ctid) from A group by id, info);
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Delete on a  (cost=74.38..131.31 rows=1397 width=6) (actual time=12.619..12.619 rows=0 loops=1)
   ->  Seq Scan on a  (cost=74.38..131.31 rows=1397 width=6) (actual time=5.146..7.129 rows=3000 loops=1)
         Filter: (NOT (hashed SubPlan 1))
         Rows Removed by Filter: 1000
         SubPlan 1
           ->  HashAggregate  (cost=70.89..73.69 rows=279 width=42) (actual time=3.762..4.155 rows=1000 loops=1)
                 Group Key: a_1.id, a_1.info
                 ->  Seq Scan on a a_1  (cost=0.00..49.94 rows=2794 width=42) (actual time=0.017..1.158 rows=4000 loops=1)
 Planning Time: 1.923 ms
 Execution Time: 44.130 ms
(10 rows)
-- 用any(array)的方式处理
postgres=# explain (analyze, costs, timing) delete from A
postgres-#  where ctid = any(array (select ctid
postgres(#                      from (select "row_number"() over(partition by id, info) as rn,
postgres(#                                   ctid
postgres(#                              from A) as ad
postgres(#                     where ad.rn > 1));
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Delete on a  (cost=300.69..340.79 rows=10 width=6) (actual time=17.686..17.686 rows=0 loops=1)
   InitPlan 1 (returns $0)
     ->  Subquery Scan on ad  (cost=209.87..300.68 rows=931 width=6) (actual time=3.995..9.503 rows=3000 loops=1)
           Filter: (ad.rn > 1)
           Rows Removed by Filter: 1000
           ->  WindowAgg  (cost=209.87..265.75 rows=2794 width=50) (actual time=3.986..8.570 rows=4000 loops=1)
                 ->  Sort  (cost=209.87..216.86 rows=2794 width=42) (actual time=3.974..4.577 rows=4000 loops=1)
                       Sort Key: a_1.id, a_1.info
                       Sort Method: quicksort  Memory: 284kB
                       ->  Seq Scan on a a_1  (cost=0.00..49.94 rows=2794 width=42) (actual time=0.015..1.486 rows=4000 loops=1)
   ->  Tid Scan on a  (cost=0.01..40.11 rows=10 width=6) (actual time=11.130..12.945 rows=3000 loops=1)
         TID Cond: (ctid = ANY ($0))
 Planning Time: 0.619 ms
 Execution Time: 17.808 ms
(14 rows)
结论：
1、效率大大提升；数据量越大提升效果越好；any（array） 的效果 >= in
2、判断 array 所含元素的方法,有 any / some (any) 还有 all两种方法

2|02、array 相关函数

-- string 转换 array
-- 函数 string_to_array
select array_to_string(array[1, 2, 3], '~^~');
 array_to_string 
-----------------
 1~^~2~^~3
-- 函数 string_to_array
select string_to_array('1~^~2~^~3','~^~');
 string_to_array 
-----------------
 {1,2,3}
-- 函数 regexp_split_to_array；跟string_to_array有点类似
select regexp_split_to_array('1~^~2~^~3','\~\^\~');
 regexp_split_to_array 
-----------------------
 {1,2,3}
-- 函数 unnest
select  unnest(array['a', 'b', 'c']);
 unnest 
--------
 a
 b
 c
-- 还可以结合with ordinality；添加行号
select * from unnest(array['a', 'b', 'c']) with ordinality;
 unnest | ordinality 
--------+------------
 a      |          1
 b      |          2
 c      |          3

3|03、数组列支持索引

在PostgreSQL中；在数组列还支持索引。对数组够友好的吧；其他数据库至少现在连苗头还没有。

在PostgreSQL中数组列支持的索引类型是GIN索引；即俗称‘倒排索引’，常用于多值列上；例如json类型，数组类型，多列上，以及全文检索上。可高效检索某值是否存在。

postgres=> explain analyze SELECT * FROM tbl_contacts WHERE phone @> array['18800001921'::varchar(32)];
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl_contacts  (cost=29.69..2298.29 rows=1250 width=57) (actual time=0.031..0.031 rows=1 loops=1)
   Recheck Cond: (phone @> '{18800001921}'::character varying(32)[])
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_contacts_phone  (cost=0.00..29.37 rows=1250 width=0) (actual time=0.023..0.023 rows=1 loops=1)
         Index Cond: (phone @> '{18800001921}'::character varying(32)[])
 Planning Time: 0.097 ms
 Execution Time: 0.055 ms
(7 rows)

__EOF__

本文作者：lottu
本文链接：https://www.cnblogs.com/lottu/p/13730272.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！