Substituting a value into an empty field after using split_part

Question

I have two columns, id integerand version text. I am trying to convert strings to versionintegers so that I can select the maximum (most recent) version of id.

However, the first instance idretains itself as version. Example:

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

Unlike:

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

Additional lines correspond to the identifier of the agreement: 10, version: 10-1. Etc.

How can i do this? I tried split_part()and pronounced how int. However, it split_part(version, "-", 2)will return what looks like an empty string. I tried to run this using to COALESCE(splitpart..., '0')no avail, as he tried to read the empty field returned by the index of field 2.

Answer 1

, , CASE:

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

, , int, , , , .

, ROW_NUMBER(). id, CASE .

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;

:

Rextester

Answer 2

coalesce() nullif(), :

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)    

Answer 3

split_part() ('') - not NULL - . COALESCE . ('') integer, .

GREATEST(split_part( ... ) , '0') , , NULL ( ). DISTINCT ON (), " " version id.

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

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

:

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

NULLIF NULLS LAST ( ) :

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

.

Or a more explicit statement CASE:

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

dbfiddle here

on this topic:

• Order varchar string as numeric
• Select the first row in each GROUP BY?
• Sort PostgreSQL by asc date time, null first?
• How to convert empty to null in PostgreSQL?