Array data structures can be used in PostgreSQL, e.g.
select array[1,2,3];
return {1,2,3}
But, if
select array[1,2,3][1]; --Will report an error select (select array[1,2,3])[1] --Available
Then you will encounter such a problem when using the regular matching function regexp_match, such as
select regexp_match('123-123', '(\d+)-(\d+)'); --return {123, 123}
select regexp_match('123-123', '(\d+)-(\d+)')[1]; --Report an error
However, if you want to get one of the elements, you have to use nested queries like
select(select regexp_match('123-123', '(\d+)-(\d+)'))[1]; --return 123
Secondly, if you want to use regexp_matches to add global search, you may generate multiple rows of data, such as
select 'a', array(select regexp_matches('aa-aa', '(aa)+', 'g'));
-- return 2 rows
a {aa}
a {aa}
Merge into one row, need an array function
select 'a', array(select regexp_matches('aa-aa', '(aa)+', 'g'));
--return
a {{aa},{aa}}
Take the elements in it
select a, b[1][1] from (select 'a' as a, array(select regexp_matches('aa-aa', '(aa)+', 'g')) as b) as c;
--return
aa
Supplement: PostgreSQL's array_to_string function
start
Use the second parameter to connect the array elements, for example:
postgres=# select array_to_string (ARRAY[1,2,3],'##'); array_to_string ----------------- 1##2##3 (1 row) postgres=#
End ~
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.