Wyczyść ciąg w postgresql

Question

Mam w tabeli kolumnę zawierającą dane o wszelkich aktualizacjach związanych ze zmianami w firmie w poniższym formacie -

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

Jak widać powyżej, kolumna updateszawiera ciągi zawierające znaki nowej linii i opisujące jedną lub wiele aktualizacji. W powyższym przykładzie oznacza to, że dla ID firmy 101 nazwa została zmieniona z ABC na XYZ, a adres URL zmienił się z www.abc.com na www.xyz.com . W przypadku identyfikatora firmy 109 zmieniono jedynie ocenę z 4,5 na 4,0.

Chciałbym jednak podzielić kolumnę aktualizacji na 3 kolumny - jedna powinna zawierać to, co zostało zmienione (url, nazwa itp.), Druga powinna mieć starą wartość, a trzecia kolumna powinna mieć nową wartość. Coś takiego -

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

Robię to w Postgres i wiem, jak wyodrębnić podciągi na podstawie znaków, ale wydaje mi się to nieco skomplikowane, ponieważ muszę wyodrębnić wiele podciągów z tej samej kolumny dla każdego wiersza. Każda pomoc będzie mile widziana. Dzięki!

Answer 1

Na początku możesz użyć regexp_split_into_tablewyrażenia regularnego i wyrażenia regularnego z dodatnim wyprzedzeniem, aby uzyskać wersję tabeli, w której każdy z wierszy zawiera dokładnie jedną aktualizację:

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

Spowoduje to podzielenie kolumny updatesw dowolnym punkcie nowej linii ( \n), po której następuje pojedyncze słowo i dwukropek ( \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           |
+-------------+--------------+----------------+

Na tej podstawie możesz łatwiej zbudować żądany stół. Aby to zrobić, możesz użyć np. split_partDo podzielenia ciągu aktualizacji na trzy żądane części.

Łącząc to razem z pierwszą częścią, otrzymasz pełne zapytanie:

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

Oto przykład skrzypiec db <> :https://dbfiddle.uk/?rdbms=postgres_10&fiddle=92017c8f296a0d100fd856eef835e60d

Więcej szczegółów / dodatkowe informacje:

• znak nowej linii w ciągach postgres: https://stackoverflow.com/a/26638775/14015737
• Granice słów regex postgresql: https://stackoverflow.com/a/3825705/14015737
• dzielenie ciągów na nowe kolumny: https://stackoverflow.com/a/8612456/14015737