【PostgreSQL】PostgreSQL对数组中的元素做模糊匹配

---

一、示范

如果你想要匹配数组中所有元素，可以使用array_length函数和generate_subscripts函数结合LIKE或ILIKE：

SELECT *
FROM products
WHERE EXISTS (
  SELECT 1
  FROM generate_subscripts(tags, 1) AS t(i)
  WHERE tags[t.i] LIKE 'pattern%'
);

请注意，这些查询假设tags是一个一维数组。如果它是一个多维数组，你需要调整generate_subscripts函数的参数以适配数组的维度。

二、实操

2.1、创建测试表并刷数

drop table if exists tzq_db_cfg_t;
drop sequence if exists tzq_db_cfg_s;
create sequence if not exists tzq_db_cfg_s;
CREATE TABLE if not exists tzq_db_cfg_t (
  db_cfg_id int8 not null PRIMARY KEY DEFAULT nextval('tzq_db_cfg_s'),
  connect_name VARCHAR(1000),
  database_type VARCHAR(1000),
  host VARCHAR(1000),
  port int8,
  dbname VARCHAR(1000),
  username VARCHAR(1000),
  password VARCHAR(1000),
  created_by int8 NOT NULL DEFAULT -1,
  creation_date TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_updated_by int8 NOT NULL DEFAULT -1,
  last_update_date TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  description VARCHAR(3000)
);
CREATE UNIQUE index uk_tzq_db_cfg_1 on tzq_db_cfg_t (connect_name);
/* 刷数 */
insert into tzq_db_cfg_t(connect_name, database_type, host, port, dbname, username, password, description)
select 'gauss_tzq_dev', 'openGauss', '127.0.0.1', 8000, 'tzq_log_sit_db', 'tzq_log', 'Tzq@12345', 'tzq的dev环境的高斯数据库';
insert into tzq_db_cfg_t(connect_name, database_type, host, port, dbname, username, password, description)
select 'pg_tzq_pro', 'PostgreSQL', '127.0.0.1', 5432, 'tzq_pro', 'tzq_log', 'Tzq@54321', 'tzq的pro环境的PG数据库';

drop table if exists compare_task_t cascade;
drop sequence if exists compare_task_s;
create sequence if not exists compare_task_s;
create table compare_task_t (
  task_id int8 not null DEFAULT nextval('compare_task_s'),
  task_name varchar(300),
  db_cfg_1_id int8,
  db_cfg_2_id int8,
  db_cfg_3_id int8,
  db_cfg_4_id int8,
  db_cfg_5_id int8,
  db_cfg_6_id int8,
  db_cfg_7_id int8,
  created_by int8 NOT NULL DEFAULT -1,
  creation_date timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_updated_by int8 NOT NULL DEFAULT -1,
  last_update_date timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  description varchar(500),
  CONSTRAINT pk_compare_task_t PRIMARY KEY (task_id)
);
insert into compare_task_t
(task_name,db_cfg_1_id,db_cfg_2_id,db_cfg_3_id,db_cfg_4_id,db_cfg_5_id,db_cfg_6_id,db_cfg_7_id)
select 'tzq比对任务', 1, 2, null::int8, null::int8, null::int8, null::int8, null::int8;
insert into compare_task_t
(task_name,db_cfg_1_id,db_cfg_2_id,db_cfg_3_id,db_cfg_4_id,db_cfg_5_id,db_cfg_6_id,db_cfg_7_id)
select 'tzq比对任务2', 1, null::int8, null::int8, null::int8, null::int8, null::int8, null::int8;

2.2、PostgreSQL对数组中的元素做模糊匹配

/* PostgreSQL对数组中的元素做模糊匹配 */
select * 
  from (select ARRAY[connect_name1, connect_name2, connect_name3,
                     connect_name4, connect_name5, connect_name6, connect_name7] as a1
              ,t.* 
          from (select t.task_id as id
                      ,t.task_name
                      ,t.db_cfg_1_id ,a1.connect_name as connect_name1
                      ,t.db_cfg_2_id ,a2.connect_name as connect_name2
                      ,t.db_cfg_3_id ,a3.connect_name as connect_name3
                      ,t.db_cfg_4_id ,a4.connect_name as connect_name4
                      ,t.db_cfg_5_id ,a5.connect_name as connect_name5
                      ,t.db_cfg_6_id ,a6.connect_name as connect_name6
                      ,t.db_cfg_7_id ,a7.connect_name as connect_name7
                      ,t.created_by
                      ,to_char(t.creation_date, 'yyyy-mm-dd hh:mi:ss') AS creation_date
                      ,t.last_updated_by
                      ,to_char(t.last_update_date, 'yyyy-mm-dd hh:mi:ss') AS last_update_date
                      ,t.description
                  from compare_task_t t
                  left join tzq_db_cfg_t a1 on a1.db_cfg_id = t.db_cfg_1_id
                  left join tzq_db_cfg_t a2 on a2.db_cfg_id = t.db_cfg_2_id
                  left join tzq_db_cfg_t a3 on a3.db_cfg_id = t.db_cfg_3_id
                  left join tzq_db_cfg_t a4 on a4.db_cfg_id = t.db_cfg_4_id
                  left join tzq_db_cfg_t a5 on a5.db_cfg_id = t.db_cfg_5_id
                  left join tzq_db_cfg_t a6 on a6.db_cfg_id = t.db_cfg_6_id
                  left join tzq_db_cfg_t a7 on a7.db_cfg_id = t.db_cfg_7_id) t
       ) q 
 where 1=1
   and EXISTS (
      SELECT 1
        FROM generate_subscripts(q.a1, 1) AS t(i)
       /* like 方式 */
       -- WHERE q.a1[t.i] like 'pg\_%'
       /* 正则匹配 */
       WHERE q.a1[t.i]  ~* concat('^.*', '需要匹配的字符串', '.*$')
);

2.3、测试查询结果

/* 测试查询结果 */
1、“需要匹配的字符串” 如果等于 dev ，则查询出1条记录
2、“需要匹配的字符串” 如果等于 tzq ，则查询出2条记录