Sustituyendo valor en campo vacío después de usar split_part

Tengo dos columnas, id integer y version text . Estoy tratando de convertir las cadenas en la version en integers para que pueda seleccionar la versión máxima (más reciente) de la identificación.

Sin embargo, la primera instancia del id almacena como la version . Ejemplo:

 id | version ---+-------- 10 | '10' 

Opuesto a:

 id | version ---+-------- 10 | '10-0' 

Las filas adicionales siguen la identificación de la convención: 10, versión: 10-1. Etc.

¿Cómo puedo lograr esto? He intentado split_part() y hechizo como int . Sin embargo, split_part(version, "-", 2) devolverá lo que parece una cadena vacía. He intentado ejecutar esto utilizando un COALESCE(splitpart..., '0') en vano ya que intentó leer el campo vacío devuelto por el índice de campo 2.

Para evitar las cadenas de versión que no tienen guión, puede usar una expresión CASE :

 CASE WHEN version LIKE '%-%' THEN SPLIT_PART(version, '-', 2)::int ELSE 0 END 

La idea básica es usar el número de versión, convertir a un int, cuando un guión está presente, pero de lo contrario asumir que la versión es cero si el guión está ausente.

Con este obstáculo fuera del path, su consulta ahora solo se networkinguce a una consulta ROW_NUMBER() . Aquí, la partición es la id , y el order se da usando la expresión CASE anterior para la versión.

 SELECT t.id, t.version FROM ( SELECT id, CASE WHEN version LIKE '%-%' THEN version ELSE version || '-0' END AS version, ROW_NUMBER() OVER (PARTITION BY id ORDER BY CASE WHEN version LIKE '%-%' THEN SPLIT_PART(version, '-', 2)::int ELSE 0 END DESC) rn FROM yourTable ) t WHERE t.rn = 1 ORDER BY t.id; 

Demostración aquí:

Rextester

Use la combinación de coalesce () y nullif (), ejemplo:

 with my_table(version) as ( values ('10'), ('10-1'), ('10-2') ) select version, split_part(version, '-', 1)::int as major, coalesce(nullif(split_part(version, '-', 2), ''), '0')::int as minor from my_table version | major | minor ---------+-------+------- 10 | 10 | 0 10-1 | 10 | 1 10-2 | 10 | 2 (3 rows) 

split_part() devuelve la cadena vacía ( '' ) – no NULL – cuando la parte a devolver está vacía o no existe. Es por eso que COALESCE no hace nada aquí. Y la cadena vacía ( '' ) no tiene representación como valor integer , por lo tanto, arroja un error cuando intenta lanzarlo.

La forma más corta en este ejemplo debe ser GREATEST(split_part( ... ) , '0') antes de la conversión, ya que la cadena vacía se ordera antes que cualquier otra cadena no vacía o incluso NULL (en cualquier configuration regional). Luego use DISTINCT ON () para get la fila con la version "más grande" para cada id .

Configuración de testing

 CREATE TABLE tbl ( id integer NOT NULL , version text NOT NULL ); INSERT INTO tbl VALUES (10, '10-2') , (10, '10-1') , (10, '10') -- missing subversion , (10, '10-111') -- multi-digit number , (11, '11-1') , (11, '11-0') -- proper '0' , (11, '11-') -- missing subversion but trailing '-' , (11, '11-2'); 

Soluciones

 SELECT DISTINCT ON (id) * FROM tbl ORDER BY id, GREATEST(split_part(version, '-', 2), '0')::int DESC; 

Resultado:

  id | version ----+--------- 10 | 10-111 11 | 10-2 

O también puede usar NULLIF y usar NULLS LAST (en order descendente) para orderar:

 SELECT DISTINCT ON (id) * FROM tbl ORDER BY id, NULLIF(split_part(version, '-', 2), '')::int DESC NULLS LAST; 

Mismo resultado.

O una statement CASE más explícita:

 CASE WHEN split_part(version, '-', 2) = '' THEN '0' ELSE split_part(version, '-', 2) END 

dbfiddle aquí

Relacionado:

  • Ordenar cadena varchar como numérica
  • Seleccione la primera fila en cada grupo GROUP BY?
  • PostgreSQL orderar por datetime asc, null primero?
  • ¿Cómo convertir de vacío a nulo en PostgreSQL?