Stringa pulita in postgresql

Question

Ho una colonna in una tabella che contiene dati su eventuali aggiornamenti relativi a modifiche su un'azienda nel formato seguente:

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

Come puoi vedere sopra, la colonna updatescontiene stringhe che includono nuove righe e descrivono uno o più aggiornamenti. Nell'esempio sopra, ciò significa che per la società ID 101, il nome è cambiato da ABC a XYZ e l'URL è cambiato da www.abc.com a www.xyz.com . Per la società ID 109, solo la valutazione è cambiata da 4,5 a 4,0.

Tuttavia, vorrei dividere la colonna degli aggiornamenti in 3 colonne: una dovrebbe contenere ciò che è stato modificato (url, nome ecc.), La seconda dovrebbe avere il vecchio valore e la terza colonna dovrebbe avere il nuovo valore. Qualcosa come questo -

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

Lo sto facendo in Postgres e so come estrarre sottostringhe in base ai caratteri, ma questo mi sembra un po 'complicato poiché ho bisogno di estrarre più sottostringhe dalla stessa colonna per ogni riga. Qualsiasi aiuto sarebbe apprezzato. Grazie!

Answer 1

All'inizio, puoi utilizzare regexp_split_into_tablee una regexp con un lookahead positivo per ottenere una versione della tua tabella in cui ciascuna delle righe contiene esattamente un aggiornamento:

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

Questo dividerà la colonna updatesin qualsiasi nuova riga ( \n) seguita da una singola parola e due punti ( \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           |
+-------------+--------------+----------------+

Da questo, puoi costruire più facilmente il tuo tavolo desiderato. Per fare questo, puoi usare ad esempio split_partper dividere la stringa di aggiornamento nelle tre parti che desideri.

Mettendo questo insieme alla prima parte ottieni la query 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
       )
;

Ecco un esempio di violino db <> :https://dbfiddle.uk/?rdbms=postgres_10&fiddle=92017c8f296a0d100fd856eef835e60d

Maggiori dettagli / informazioni aggiuntive:

• carattere di nuova riga nelle stringhe postgres: https://stackoverflow.com/a/26638775/14015737
• confini delle parole regex postgresql: https://stackoverflow.com/a/3825705/14015737
• dividere le stringhe in nuove colonne: https://stackoverflow.com/a/8612456/14015737