Nettoyer la chaîne dans postgresql

Question

J'ai une colonne dans un tableau qui contient des données sur les mises à jour liées aux changements concernant une entreprise dans le format ci-dessous -

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

Comme vous pouvez le voir ci-dessus, la colonne updatescontient des chaînes qui incluent des retours à la ligne et décrivent une ou plusieurs mises à jour. Dans l'exemple ci-dessus, cela signifie que pour l'ID d'entreprise 101, le nom est passé de ABC à XYZ et l'url est passée de www.abc.com à www.xyz.com . Pour la société ID 109, seule la note est passée de 4,5 à 4,0.

Cependant, je voudrais diviser la colonne des mises à jour en 3 colonnes - l'une doit contenir ce qui a été modifié (URL, nom, etc.), la seconde doit avoir l'ancienne valeur et la 3ème colonne doit avoir la nouvelle valeur. Quelque chose comme ça -

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

Je fais cela dans Postgres et je sais comment extraire des sous-chaînes en fonction des caractères, mais cela me semble un peu compliqué car j'ai besoin d'extraire plusieurs sous-chaînes de la même colonne pour chaque ligne. Toute aide serait appréciée. Merci!

Answer 1

Dans un premier temps, vous pouvez utiliser regexp_split_into_tableet une expression rationnelle avec une anticipation positive pour obtenir une version de votre table dans laquelle chacune des lignes contient exactement une mise à jour:

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

Cela divisera la colonne updatesà n'importe quelle nouvelle ligne ( \n) suivie d'un seul mot et d'un deux-points ( \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           |
+-------------+--------------+----------------+

À partir de là, vous pouvez créer plus facilement la table souhaitée. Pour ce faire, vous pouvez utiliser par exemple split_partpour diviser la chaîne de mise à jour en trois parties que vous voulez.

En associant ceci à la première partie, vous obtenez la requête complète:

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

Voici un exemple de db <> fiddle :https://dbfiddle.uk/?rdbms=postgres_10&fiddle=92017c8f296a0d100fd856eef835e60d

Plus de détails / informations supplémentaires:

• caractère de nouvelle ligne dans les chaînes postgres: https://stackoverflow.com/a/26638775/14015737
• limites des mots regex postgresql: https://stackoverflow.com/a/3825705/14015737
• fractionnement des chaînes en nouvelles colonnes: https://stackoverflow.com/a/8612456/14015737