Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable1 (
    mystring text
);

CREATE TABLE mytable2 (
    col1 text, col2 text, col3 text
);
INSERT INTO mytable1
VALUES ('ab bc 123 cd de ef 232'),('rs tu vw 123 xy za 232');

SELECT * FROM mytable1
2 rows affected



mystring


ab bc 123 cd de ef 232

rs tu vw 123 xy za 232


SELECT
    regexp_match(mystring,'^(.+)\s(.+)\s(\d+)\s(.+)\s(.+)\s(\d+)$') AS r
FROM mytable1


r


{ab,bc,123,"cd de",ef,232}

{"rs tu",vw,123,xy,za,232}


WITH textblocks AS (
    SELECT
        regexp_match(mystring,'^(.+)\s(.+)\s(\d+)\s(.+)\s(.+)\s(\d+)$') AS r
    FROM mytable1
)
SELECT
    r[1], r[2], r[3]
FROM textblocks

UNION

SELECT
    r[4], r[5], r[6]
FROM textblocks


r
r
r


xy
za
232

cd de
ef
232

rs tu
vw
123

ab
bc
123


WITH textblocks AS (
    SELECT
        regexp_match(mystring,'^(.+)\s(.+)\s(\d+)\s(.+)\s(.+)\s(\d+)$') AS r
    FROM mytable1
)
INSERT INTO mytable2 (col1, col2, col3)

SELECT
    r[1], r[2], r[3]
FROM textblocks

UNION

SELECT
    r[4], r[5], r[6]
FROM textblocks
4 rows affected

SELECT * FROM mytable2;


col1
col2
col3


xy
za
232

cd de
ef
232

rs tu
vw
123

ab
bc
123