Substituting Value In Empty Field After Using Split_part

February 25, 2024 Post a Comment

I have two columns, id integer and version text. I am trying to convert the strings in version into integers so that I may select the maximum (most recent) version of the id. Howev

Solution 1:

split_part() returns the empty string ('') - not NULL - when the part to be returned is empty or non-existent. That's why COALESCE does nothing here. And the empty string ('') has no representation as integer value, hence it throws an error when trying to cast it.

The shortest way in this example should be GREATEST(split_part( ... ) , '0') before casting, since the empty string sorts before any other non-empty string or even NULL (in any locale). Then use DISTINCT ON () to get the row with the "biggest" version for each id.

Test setup

CREATETABLE tbl (
   id      integerNOTNULL
 , version text    NOTNULL
);

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

Solutions

SELECTDISTINCTON (id) *FROM   tbl
ORDERBY id, GREATEST(split_part(version, '-', 2), '0')::intDESC;

Result:

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

Or you could also use NULLIF and use NULLS LAST (in descending order) to sort:

SELECTDISTINCTON (id) *FROM   tbl
ORDERBY id, NULLIF(split_part(version, '-', 2), '')::intDESC NULLS LAST;

Same result.

Or a more explicit CASE statement:

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

dbfiddle here

Solution 2:

Use the combination of coalesce() and nullif(), example:

with my_table(version) as (
values
    ('10'), ('10-1'), ('10-2')
)

select 
    version, 
    split_part(version, '-', 1)::intas major, 
    coalesce(nullif(split_part(version, '-', 2), ''), '0')::intas minor
from my_table

 version | major | minor 
---------+-------+-------10|10|010-1|10|110-2|10|2
(3rows)

Solution 3:

To get around the version strings which have no hyphen, you can use a CASE expression:

CASEWHEN version LIKE'%-%'THEN SPLIT_PART(version, '-', 2)::intELSE0END

The basic idea is to use the version number, cast to an int, when a hyphen is present, but otherwise to assume that the version is zero if the hyphen is absent.

With this hurdle out of the way, your query now just reduces to a ROW_NUMBER() query. Here, the partition is the id, and the ordering is given using the above CASE expression for the version.

SELECT
    t.id, t.version
FROM
(
    SELECT
        id,
        CASEWHEN version LIKE'%-%'THEN version
             ELSE version ||'-0'ENDAS version,
        ROW_NUMBER() OVER (PARTITIONBY id
                           ORDERBYCASEWHEN version LIKE'%-%'THEN SPLIT_PART(version, '-', 2)::intELSE0ENDDESC) rn
    FROM yourTable
) t
WHERE t.rn =1ORDERBY t.id;

Demo here:

Lab Sql