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는 줄 바꿈을 포함하고 하나 또는 여러 업데이트를 설명하는 문자열이 포함됩니다. 위의 예에서 이는 회사 ID 101의 이름이 ABC에서 XYZ로 변경되고 URL이 www.abc.com 에서 www.xyz.com으로 변경되었음을 의미합니다 . 회사 ID 109의 경우 등급 만 4.5에서 4.0으로 변경되었습니다.

그러나 업데이트 열을 3 개의 열로 나누고 싶습니다. 하나는 변경된 내용 (URL, 이름 등)을 포함해야하고 두 번째 열에는 이전 값이 있어야하며 세 번째 열에는 새 값이 있어야합니다. 이 같은 -

#============#============#==============#================#
| 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를 사용하여 각 행에 정확히 하나의 업데이트가 포함 된 테이블 버전을 가져올 수 있습니다.

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 정규식 단어 경계 : https://stackoverflow.com/a/3825705/14015737
• 문자열을 새 열로 분할 : https://stackoverflow.com/a/8612456/14015737