how to order by nested list column in postgres-CodePudding

the table is created

 CREATE TABLE "Tasks"(
    code VARCHAR ( 50 ) NOT NULL
);

query

select "code" from "Tasks" order by "code" asc

result:

how to make it so:

There are options on how to do this so that the code field is sorted as needed

how to sort the list as needed?

CodePudding user response：

If this is about semantic versioning, then you need to convert the dotted parts into numbers. You can do that by splitting the string on . and then casting the resulting array to int[]:

# with tasks (code) as (
  values ('0.1'), ('0.1.1'), ('0.1.10'), ('0.1.11'), ('0.1.2'), 
         ('0.1.3'), ('0.2.1'), ('1.1'), ('1.1.1'), ('1.1.10'), ('1.1.2')
)
select * 
  from tasks
 order by string_to_array(code, '.')::int[];

┌────────┐
│  code  │
├────────┤
│ 0.1    │
│ 0.1.1  │
│ 0.1.2  │
│ 0.1.3  │
│ 0.1.10 │
│ 0.1.11 │
│ 0.2.1  │
│ 1.1    │
│ 1.1.1  │
│ 1.1.2  │
│ 1.1.10 │
└────────┘
(11 rows)