Bersihkan string di postgresql

Question

Saya memiliki kolom di tabel yang berisi data tentang pembaruan apa pun yang terkait dengan perubahan tentang perusahaan dalam format di bawah ini -

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

Seperti yang Anda lihat di atas, kolom updatesberisi string yang menyertakan baris baru dan menjelaskan satu atau beberapa pembaruan. Dalam contoh di atas, ini berarti bahwa untuk ID perusahaan 101, nama diubah dari ABC menjadi XYZ dan url diubah dari www.abc.com menjadi www.xyz.com . Untuk ID perusahaan 109, hanya rating yang diubah dari 4.5 menjadi 4.0.

Namun saya ingin membagi kolom pembaruan menjadi 3 kolom - satu harus berisi apa yang diubah (url, nama, dll.), Kolom kedua harus memiliki nilai lama dan kolom ke-3 harus memiliki nilai baru. Sesuatu seperti ini -

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

Saya melakukan ini di Postgres dan tahu cara mengekstrak substring berdasarkan karakter tetapi ini terlihat agak rumit bagi saya karena saya perlu mengekstrak beberapa substring dari kolom yang sama untuk setiap baris. Bantuan apa pun akan dihargai. Terima kasih!

Answer 1

Pada awalnya, Anda dapat menggunakan regexp_split_into_tabledan regexp dengan lookahead positif untuk mendapatkan versi tabel Anda di mana setiap baris berisi tepat satu pembaruan:

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

Ini akan memisahkan kolom updatesdi setiap baris baru ( \n) yang diikuti oleh satu kata dan titik dua ( \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           |
+-------------+--------------+----------------+

Dari sini, Anda dapat dengan lebih mudah membuat tabel yang Anda inginkan. Untuk melakukan ini, Anda dapat menggunakan mis split_partuntuk membagi string pembaruan menjadi tiga bagian yang Anda inginkan.

Menyatukan ini dengan bagian pertama memberi Anda kueri lengkap:

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

Berikut adalah contoh biola db <> :https://dbfiddle.uk/?rdbms=postgres_10&fiddle=92017c8f296a0d100fd856eef835e60d

Lebih detail / info tambahan:

• karakter baris baru dalam string postgres: https://stackoverflow.com/a/26638775/14015737
• batas kata regex postgresql: https://stackoverflow.com/a/3825705/14015737
• memisahkan string menjadi kolom baru: https://stackoverflow.com/a/8612456/14015737