ล้างสตริงใน postgresql

Question

ฉันมีคอลัมน์ในตารางที่มีข้อมูลเกี่ยวกับการอัปเดตที่เกี่ยวข้องกับการเปลี่ยนแปลงเกี่ยวกับ บริษัท ในรูปแบบด้านล่างนี้ -

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

ดังที่คุณเห็นด้านบนคอลัมน์updatesนี้ประกอบด้วยสตริงที่มีการขึ้นบรรทัดใหม่และอธิบายการอัปเดตหนึ่งรายการหรือหลายรายการ ในตัวอย่างข้างต้นนี้หมายถึงว่าสำหรับ บริษัท รหัส 101 เปลี่ยนชื่อจากเอบีซีที่จะ XYZ และ URL เปลี่ยนจากwww.abc.comเพื่อwww.xyz.com สำหรับรหัส บริษัท 109 อันดับเท่านั้นที่เปลี่ยนจาก 4.5 เป็น 4.0

อย่างไรก็ตามฉันต้องการแบ่งคอลัมน์การอัปเดตออกเป็น 3 คอลัมน์ - คอลัมน์หนึ่งควรมีสิ่งที่เปลี่ยนแปลง (url, ชื่อ ฯลฯ ) ที่สองควรมีค่าเก่าและคอลัมน์ที่ 3 ควรมีค่าใหม่ อะไรทำนองนี้ -

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

ฉันกำลังทำสิ่งนี้ใน Postgres และรู้วิธีแยกสตริงย่อยตามอักขระ แต่สิ่งนี้ดูซับซ้อนสำหรับฉันเล็กน้อยเนื่องจากฉันต้องแยกสตริงย่อยหลายรายการจากคอลัมน์เดียวกันสำหรับแต่ละแถว ความช่วยเหลือใด ๆ จะได้รับการชื่นชม ขอบคุณ!

Answer 1

ในตอนแรกคุณสามารถใช้regexp_split_into_tableและ regexp ที่มี lookahead ในเชิงบวกเพื่อรับเวอร์ชันของตารางของคุณซึ่งแต่ละแถวมีการอัปเดตเพียงรายการเดียว:

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

ซึ่งจะแบ่งคอลัมน์updatesที่ขึ้นบรรทัดใหม่ ( \n) ใด ๆที่ตามด้วยคำเดี่ยวและเครื่องหมายทวิภาค ( \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           |
+-------------+--------------+----------------+

จากนี้คุณสามารถสร้างตารางที่คุณต้องการได้ง่ายขึ้น ในการดำเนินการนี้คุณสามารถใช้เช่นsplit_partเพื่อแยกสตริงการอัพเดตออกเป็นสามส่วนที่คุณต้องการ

การนำสิ่งนี้มารวมกับส่วนแรกจะทำให้คุณได้รับแบบสอบถามทั้งหมด:

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

นี่คือตัวอย่างdb <> fiddle :https://dbfiddle.uk/?rdbms=postgres_10&fiddle=92017c8f296a0d100fd856eef835e60d

รายละเอียดเพิ่มเติม / ข้อมูลเพิ่มเติม:

• อักขระขึ้นบรรทัดใหม่ในสตริง postgres: https://stackoverflow.com/a/26638775/14015737
• ขอบเขตของคำ postgresql regex: https://stackoverflow.com/a/3825705/14015737
• การแยกสตริงออกเป็นคอลัมน์ใหม่: https://stackoverflow.com/a/8612456/14015737