CODEWITHSUNDEEP
postgresqlpostgresql-9.5
dr.petr
dr.petr

Reputation: 25

Parse text data in PostgreSQL

I've got a PostgreSQL database, one table with 2 text columns, stored data like this:

id|         col1              |                     col2                      |
------------------------------------------------------------------------------|
1 | value_1, value_2, value_3 | name_1(date_1), name_2(date_2), name_3(date_3)|
2 | value_4, value_5, value_6 | name_4(date_4), name_5(date_5), name_6(date_6)|

I need to parse rows in a new table like this:

id |  col1   |  col2  |  col3  |
1  | value_1 | name_1 | date_1 |
1  | value_2 | name_2 | date_2 |
...|   ...   |  ...   |  ...   |
2  | value_6 | name_6 | date_6 |

How might I do this?

Upvotes: 1

Views: 1463

Answers (1)

S-Man
S-Man

Reputation: 23676

step-by-step demo:db<>fiddle

SELECT
    id,
    u_col1 as col1,
    col2_matches[1] as col2,                                     -- 5
    col2_matches[2] as col3
FROM 
    mytable,
    unnest(                                                      -- 3
        regexp_split_to_array(col1, ', '),                       -- 1
        regexp_split_to_array(col2, ', ')                        -- 2
    ) as u (u_col1, u_col2),
    regexp_matches(u_col2, '(.+)\((.+)\)') as col2_matches       -- 4
  1. Split the data of your first column into an array
  2. Split the data of your second column into an array of form {a(a), b(b), c(c)}
  3. Transpose all array elements into own records
  4. Split the elements of form a(b) into an array of form {a,b}
  5. Show required columns. For the col2 and col3 show the first or the second array element from step 4

Upvotes: 1

Related Questions