Чистая строка в postgresql

Question

У меня есть столбец в таблице, содержащий данные о любых обновлениях, связанных с изменениями в компании, в следующем формате:

#=============#==============#================#
| Company ID  |  updated_at  |   updates      |
#=============#==============#================#
| 101         | 2020-11-01   | name:          |
|             |              | -ABC           |
|             |              | -XYZ           |
|             |              | url:           |
|             |              | -www.abc.com   |
|             |              | -www.xyz.com   |
+-------------+--------------+----------------+
| 109         | 2020-10-20   | rating:        |
|             |              | -4.5           |
|             |              | -4.0           |
+-------------+--------------+----------------+

Как вы можете видеть выше, столбец updatesсодержит строки, которые включают символы новой строки и описывают одно или несколько обновлений. В приведенном выше примере это означает, что для идентификатора компании 101 имя изменилось с ABC на XYZ, а URL-адрес изменился с www.abc.com на www.xyz.com . Только для компании с идентификатором 109 изменился рейтинг с 4,5 на 4,0.

Однако я хотел бы разделить столбец обновлений на 3 столбца - один должен содержать то, что было изменено (URL, имя и т. Д.), Второй должен иметь старое значение, а третий столбец должен иметь новое значение. Что-то вроде этого -

#============#============#==============#================#
| Company ID |   Field    |  Old Value   |   New Value    |
#============#============#==============#================#
| 101        |   name     | ABC          | XYZ            |
+------------+------------+--------------+----------------+
| 101        |   url      | www.abc.com  | www.xyz.com    |
+------------+------------+--------------+----------------+
| 109        |   rating   | 4.5          | 4.0            |
+------------+------------+--------------+----------------+

Я делаю это в Postgres и знаю, как извлекать подстроки на основе символов, но мне это кажется немного сложным, поскольку мне нужно извлечь несколько подстрок из одного столбца для каждой строки. Любая помощь будет оценена. Благодаря!

Answer 1

Сначала вы можете использовать regexp_split_into_tableи регулярное выражение с положительным прогнозом, чтобы получить версию вашей таблицы, в которой каждая строка содержит ровно одно обновление:

select companyID, 
       updated_at, 
       regexp_split_to_table(updates, '\n(?=\y.+:)') as updates 
  from old;

Это разделит столбец updatesна любую новую строку ( \n), за которой следует одно слово и двоеточие ( \y.+:).

#=============#==============#================#
| companyID   |  updated_at  |   updates      |
#=============#==============#================#
| 101         | 2020-11-01   | name:          |
|             |              | -ABC           |
|             |              | -XYZ           |
+-------------+--------------+----------------+
| 101         | 2020-11-01   | url:           |
|             |              | -www.abc.com   |
|             |              | -www.xyz.com   |
+-------------+--------------+----------------+
| 109         | 2020-10-20   | rating:        |
|             |              | -4.5           |
|             |              | -4.0           |
+-------------+--------------+----------------+

Исходя из этого, вам будет легче построить желаемый стол. Для этого вы можете использовать, например, split_partдля разделения строки обновления на три части.

Собрав это вместе с первой частью, вы получите полный запрос:

select companyID, 
       updated_at, 
       split_part(updates, E':', 1) as field, 
       split_part(updates, E'\n-', 2) as old_value, 
       split_part(updates, E'\n-', 3) as new_value  
  from (select companyID, 
               updated_at, 
               regexp_split_to_table(updates, '\n(?=\y.+:)') as updates 
          from old
       )
;

Вот пример скрипта db <> :https://dbfiddle.uk/?rdbms=postgres_10&fiddle=92017c8f296a0d100fd856eef835e60d

Подробнее / дополнительная информация:

• символ новой строки в строках postgres: https://stackoverflow.com/a/26638775/14015737
• Границы слов регулярного выражения postgresql: https://stackoverflow.com/a/3825705/14015737
• разбиение строк на новые столбцы: https://stackoverflow.com/a/8612456/14015737