Limpe a string no postgresql

Question

Eu tenho uma coluna em uma tabela que contém dados sobre quaisquer atualizações relacionadas a mudanças sobre uma empresa no formato abaixo -

#=============#==============#================#
| 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           |
+-------------+--------------+----------------+

Como você pode ver acima, a coluna updatescontém strings que incluem novas linhas e descrevem uma ou várias atualizações. No exemplo acima, isso significa que para a ID da empresa 101, o nome mudou de ABC para XYZ e o url mudou de www.abc.com para www.xyz.com . Para a empresa ID 109, apenas a classificação mudou de 4,5 para 4,0.

No entanto, gostaria de dividir a coluna de atualizações em 3 colunas - uma deve conter o que foi alterado (url, nome etc.), a segunda deve ter o valor antigo e a 3ª coluna deve ter o novo valor. Algo assim -

#============#============#==============#================#
| 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            |
+------------+------------+--------------+----------------+

Estou fazendo isso no Postgres e sei como extrair substrings com base em caracteres, mas isso parece um pouco complicado para mim, pois preciso extrair várias substrings da mesma coluna para cada linha. Qualquer ajuda seria apreciada. Obrigado!

Answer 1

No início, você pode usar regexp_split_into_tableuma regexp com uma antevisão positiva para obter uma versão de sua tabela em que cada uma das linhas contenha exatamente uma atualização:

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

Isso dividirá a coluna updatesem qualquer nova linha ( \n) seguida por uma única palavra e dois pontos ( \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           |
+-------------+--------------+----------------+

A partir disso, você pode construir mais facilmente a mesa desejada. Para fazer isso, você pode usar, por exemplo, split_partpara dividir a string de atualização nas três partes que desejar.

Juntando isso com a primeira parte, você obtém a consulta completa:

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
       )
;

Aqui está um exemplo de db <> violino :https://dbfiddle.uk/?rdbms=postgres_10&fiddle=92017c8f296a0d100fd856eef835e60d

Mais detalhes / informações adicionais:

• caractere de nova linha em strings postgres: https://stackoverflow.com/a/26638775/14015737
• Limites de palavras do regex postgresql: https://stackoverflow.com/a/3825705/14015737
• divisão de strings em novas colunas: https://stackoverflow.com/a/8612456/14015737