select regexp_split_to_table('hello world', '\s+') ;
select regexp_split_to_array('hello world', '\s+') ;
postgres=# select regexp_split_to_table('hello world', '\s+') ;
regexp_split_to_table
-----------------------
hello
world
(2 rows)
postgres=# select regexp_split_to_array('hello world', '\s+') ;
regexp_split_to_array
-----------------------
{hello,world}
(1 row)
nas=# drop table test;
DROP TABLE
nas=# create table test(id int);
CREATE TABLE
nas=# insert into test select n from generate_series(1,100) n;
INSERT 0 100
nas=# select id from test where id<10;
id
----
1
2
3
4
5
6
7
8
9
(9 rows)
nas=# select array(select id from test where id<10);
array
---------------------
{1,2,3,4,5,6,7,8,9}
(1 row)
nas=# select unnest(array(select id from test where id<10));
unnest
--------
1
2
3
4
5
6
7
8
9
(9 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
实例2
create table test(id int, info text);
insert into test values(1,'a');
insert into test values(1,'b');
select * from test;
select id, string_agg(info,';' ) from test group by id;
select id, array_agg(info ) from test group by id;
postgres=# create table test(id int, info text);
CREATE TABLE
postgres=# insert into test values(1,'a');
INSERT 0 1
postgres=# insert into test values(1,'b');
INSERT 0 1
postgres=# select * from test;
id | info
----+------
1 | a
1 | b
(2 rows)
postgres=# select id, string_agg(info,';' ) from test group by id;
id | string_agg
----+------------
1 | a;b
(1 row)
postgres=# select id, array_agg(info ) from test group by id;
id | array_agg
----+-----------
1 | {a,b}
(1 row)
create table test2(id int, info text);
insert into test2 values(1,'a;b');
select * from test2;
select id, regexp_split_to_table(info,';') from test2;
postgres=# create table test2(id int, info text);
CREATE TABLE
postgres=# insert into test2 values(1,'a;b');
INSERT 0 1
postgres=# select * from test2;
id | info
----+------
1 | a;b
(1 row)
postgres=# select id, regexp_split_to_table(info,';') from test2;
id | regexp_split_to_table
----+-----------------------
1 | a
1 | b
(2 rows)
