How to normalize table1 in postgres sql?

Question

I need to normalize (in a technical way) table1 by splitting the column "error occured at" so that it was like in table2.

Is there is any DML function that will help me solve this? So that the rest of the information was transferred without using lead-lag function?

I tried to used string_to_array and transpose functions.

Answer 1

WITH split_dates AS (
  SELECT other_columns..., unnest(string_to_array(error_occured_at, ', '))::date AS date
  FROM table1
  WHERE error_occured_at RLIKE "," -- Only considering rows that actually do contain a comma
)
INSERT INTO table1 (other_columns..., error_occured_at)
SELECT other_columns..., date
FROM split_dates;

If you want to then delete rows where error_occured_at is of the older format, you can do:

DELETE FROM table1
WHERE error_occured_at RLIKE ","

Answer 2

You can do it by combine unnest with string_to_array as follows :

SELECT store_name, subject_of_collection, cost, checkpoint, errors_found, s. error_occured_at
FROM   table1, unnest(string_to_array(error_occured_at, ', ')) as s(error_occured_at)