Cadena limpia en postgresql

Question

Tengo una columna en una tabla que contiene datos sobre las actualizaciones relacionadas con los cambios sobre una empresa en el siguiente formato:

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

Como puede ver arriba, la columna updatescontiene cadenas que incluyen nuevas líneas y describen una o varias actualizaciones. En el ejemplo anterior, esto significa que para el ID de empresa 101, el nombre cambió de ABC a XYZ y la URL cambió de www.abc.com a www.xyz.com . Para el ID de empresa 109, solo la calificación cambió de 4.5 a 4.0.

Sin embargo, me gustaría dividir la columna de actualizaciones en 3 columnas: una debe contener lo que se cambió (URL, nombre, etc.), la segunda debe tener el valor anterior y la tercera columna debe tener el nuevo valor. Algo como esto -

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

Estoy haciendo esto en Postgres y sé cómo extraer subcadenas basadas en caracteres, pero esto me parece un poco complicado ya que necesito extraer varias subcadenas de la misma columna para cada fila. Cualquier ayuda sería apreciada. ¡Gracias!

Answer 1

Al principio, puede usar regexp_split_into_tabley una expresión regular con una anticipación positiva para obtener una versión de su tabla en la que cada una de las filas contiene exactamente una actualización:

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

Esto dividirá la columna updatesen cualquier nueva línea ( \n) seguida de una sola palabra y dos puntos ( \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           |
+-------------+--------------+----------------+

A partir de esto, puede construir más fácilmente la mesa deseada. Para hacer esto, puede usar, por ejemplo, split_partpara dividir la cadena de actualización en las tres partes que desee.

Al poner esto junto con la primera parte, obtendrá la consulta 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
       )
;

Aquí hay un ejemplo de violín db <> :https://dbfiddle.uk/?rdbms=postgres_10&fiddle=92017c8f296a0d100fd856eef835e60d

Más detalles / información adicional:

• carácter de nueva línea en cadenas de postgres: https://stackoverflow.com/a/26638775/14015737
• Límites de palabras de expresiones regulares de postgresql: https://stackoverflow.com/a/3825705/14015737
• dividir cadenas en nuevas columnas: https://stackoverflow.com/a/8612456/14015737