PostgreSQL Multi-meaning Array Retrieval and Condition Filtering

Image for post
Image for post

Background

PostgreSQL’s array type is widely used across portrait and tag systems. In some business reconstruction, the definition of the array content often contains multiple meanings. For example, it contains both, the tag as well as the attributes of the tag such as the tag value, weighted value, and time.

An Example: PostgreSQL Multi-meaning Array

Let’s consider the following example to understand how to search for tags and filter records that match the tag’s weighted value:

create table tbl(id int, info text[]);
insert into tbl values (1, array['a:100', 'b:10']);  

insert into tbl values (2, array['a:15', 'b:20', 'c:99']);

insert into tbl values (3, array['c:78', 'b:100']);

postgres=# select * from tbl;
id | info
----+------------------
1 | {a:100,b:10}
2 | {a:15,b:20,c:99}
3 | {c:78,b:100}
(3 rows)
create or replace function get_label(text[]) returns text[] as $$  
select array(select substring(unnest($1), '(.*):'));
$$ language sql strict immutable;

postgres=# select get_label(info) from tbl;
get_label
-----------
{a,b}
{a,b,c}
{c,b}
(3 rows)
create index idx_tbl1 on tbl using gin (get_label(info));  

postgres=# explain select * from tbl where get_label(info) @> array['a'];
QUERY PLAN
-----------------------------------------------------------------------
Bitmap Heap Scan on tbl (cost=2.40..3.86 rows=1 width=36)
Recheck Cond: (get_label(info) @> '{a}'::text[])
-> Bitmap Index Scan on idx_tbl1 (cost=0.00..2.40 rows=1 width=0)
Index Cond: (get_label(info) @> '{a}'::text[])
(4 rows)
create or replace function get_weight(text[], text) returns text as $$  
select substring($1[array_position(get_label($1), $2)], ':(.*)');
$$ language sql strict immutable;


postgres=# select info, get_weight(info, 'a') from tbl;
info | get_weight
------------------+------------
{a:100,b:10} | 100
{a:15,b:20,c:99} | 15
{c:78,b:100} |
(3 rows)
postgres=# select * from tbl where get_label(info) @> array['a'] and get_weight(info, 'a')::float8 >20;  
id | info
----+--------------
1 | {a:100,b:10}
(1 row)

postgres=# explain select * from tbl where get_label(info) @> array['a'] and get_weight(info, 'a')::float8 >20;
QUERY PLAN
--------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl (cost=2.40..4.12 rows=1 width=36)
Recheck Cond: (get_label(info) @> '{a}'::text[])
Filter: ((get_weight(info, 'a'::text))::double precision > '20'::double precision)
-> Bitmap Index Scan on idx_tbl1 (cost=0.00..2.40 rows=1 width=0)
Index Cond: (get_label(info) @> '{a}'::text[])
(5 rows)

How to Modify an Array and Append an Array Element Using UPSERT

Follow the steps listed below to modify or append an array element. Refer this document to get an overview of various array operators and functions used in the steps below:

array_append(anyarray, anyelement)  

array_cat(anyarray, anyarray)

array_fill(anyelement, int[], [, int[]])

array_prepend(anyelement, anyarray)
array_replace(anyarray, anyelement, anyelement)
array_remove(anyarray, anyelement)

An Example

Following example illustrates the array modification and appending of an array element:

insert into tbl values (1, ?) on conflict (id) do update set info=func(tbl.info,?);    
create table tbl1(id int primary key, info int[]);

postgres=# insert into tbl1 values (1, array[1,2,3]) on conflict (id) do update set info=array_append(tbl1.info, 100) returning *;
id | info
----+---------
1 | {1,2,3}
(1 row)

INSERT 0 1
postgres=# insert into tbl1 values (1, array[1,2,3]) on conflict (id) do update set info=array_append(tbl1.info, 100) returning *;
id | info
----+-------------
1 | {1,2,3,100}
(1 row)

INSERT 0 1
postgres=# insert into tbl1 values (1, null) on conflict (id) do update set info=array_append(tbl1.info, 100) returning *;
id | info
----+-----------------
1 | {1,2,3,100,100}
(1 row)

INSERT 0 1

Original Source:

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store