order a table in postgresql based in a specific string field with format number/year

Question

I have internal communication table that has a field called ci_num and it's format is number/year. ex: 23/2020.

I would like to order my table based in this field. I've searched but didn't find how to create a function to order this way.

In java, I do this using a custom sorting function. I've tried to do the same thing in postgres but without success.

using ascending:

select * from cis c order by ci_num asc

result:

ci_id|ci_num |tipo_cod|status_cod|setor_cod|usuario_id
-----|-------|--------|----------|---------|----------
   10|10/2020|GE      |EC        |NUTEC    |         1
   11|11/2020|CO      |DB        |NUCAD    |         4
    1|1/2020 |CO      |DE        |NUCAD    |         1
   12|12/2020|CO      |NG        |NUTEC    |         4
   13|13/2020|CO      |AT        |NUOPE    |         4
   14|14/2020|CO      |NG        |NUTEC    |         4
   15|15/2020|GE      |DE        |NUOPE    |         4
   16|16/2020|CO      |NG        |NUTEC    |         4
   17|17/2020|CO      |CA        |NUTEC    |         4

using descending gets worse:

select * from cis c order by ci_num desc

ci_id|ci_num |tipo_cod|status_cod|setor_cod|usuario_id
-----|-------|--------|----------|---------|----------
    9|9/2020 |IN      |AT        |NUOPE    |         4
    8|8/2020 |CO      |NG        |NUTEC    |         4
    7|7/2020 |GE      |CA        |NUTEC    |         4
    6|6/2020 |HE      |CR        |NUCAD    |         1
   54|54/2020|GE      |EA        |NUTEC    |         4
   53|53/2020|GE      |EA        |NUOPE    |         1
   52|52/2020|GE      |EA        |NUOPE    |         1

Thanks for your help!

Change the schema and use two separate columns, one for the year and another for the number. — sticky bit, May 14 '20 at 23:50
Yes. Or well, in you special case probably "kind" of. Read ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad?r=SearchResults&s=1|193.1284). At least it forces you to use the wrong data type. That makes things more complicated at best. Without extra measures nothing stops a string value of `'foobar'` going into that column. If you have two `integer` columns such nonsense cannot be entered. — sticky bit, May 15 '20 at 00:05

score 1 · Accepted Answer · answered May 14 '20 at 23:43

1

You could just use string functions:

order by 
    substring(ci_num from '\d+$'), 
    substring(ci_num from '^\d+')::int

answered May 14 '20 at 23:43

GMB

216,147
25
84
135

I added a ::int in your code, cause if the year 10000 arrives it gets the wrong position XD.order by substring(ci_num from '\d+$')::int, substring(ci_num from '^\d+')::int – bksoares May 15 '20 at 00:19
@bksoares: I left it apart intentionaly so the expression is less expensive, but ok, that's a good point... although, by year 10000, this answer might not be valid anymore! – GMB May 15 '20 at 00:21
1

yep. Thank you for the explanation. – bksoares May 15 '20 at 00:23

Gordon Linoff · Answer 2 · 2020-05-14T23:50:43.363

0

Arg! You need to split the value:

order by right(ci_num, 4),
         split_part(ci_num, '/', 1)::int

Here is a db<>fiddle.

edited May 14 '20 at 23:50

answered May 14 '20 at 23:42

Gordon Linoff

1,242,037
58
646
786

order a table in postgresql based in a specific string field with format number/year

2 Answers2