postgresql - trouble to order a list obtained by string_agg

Question

I obtained a list of numbers (and text) by a string_agg.

For instance, that :

"09,12,20,200,217,400,500,90,91,92,Exp3"

This is the string_agg:

string_agg(DISTINCT t_ligne.ligne_code::text, ','::text ORDER BY (t_ligne.ligne_code::text)) AS lignes

My trouble with the ordering: I need an alphanumeric order like:

"09,12,20,90,91,92,200,217,400,500,Exp3"

If I try a CAST, it refuses to accept text "numbers" like "Exp3".

So I didn't find any way to order it like values with text objects at the end.

EDIT I don't know how to use WITH in my query without cutting my results! In fact, this is the total query:

SELECT l_arret_ligne.idap,
       t_arret.nom_arret AS nom,
       string_agg(DISTINCT t_ligne.ligne_code::text, ','::text ORDER BY (t_ligne.ligne_code::text)) AS lignes
FROM l_arret_ligne,
     t_ligne,
     t_arret
WHERE l_arret_ligne.id_ligne::text = t_ligne.id_ligne::text 
  AND l_arret_ligne.idap::text = t_arret.idap::text 
  AND t_ligne.ligne_type::text <> 'Scolaire'::text
GROUP BY l_arret_ligne.idap, t_arret.nom_arret

As my string_agg is inside a global query, I don't understand how it would be possible to cut it with WITH and globalize it at the end.

Maybe do I understand bad...

Thanks again!

EDIT 2: Hello again! I found one very good solution to order my results:

SELECT string_agg("rla"."t_ligne_19032019"."ligne_code", ',' ORDER BY case when (UPPER(ligne_code)=LOWER(ligne_code))=true then substring(concat('000000000000000',ligne_code),length(ligne_code)+1,15) else ligne_code end) AS lignes
FROM "rla"."t_ligne_19032019"

It works very well. BUT always the same trouble with DISTINCT! If I add DISTINCT in the string_agg, same message:

ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list

Any solution? Any advice?

Thanks again!

Cyril

Answer 1

The order by in a string_agg() can absolutely use a CASE expression:

with data (ligne_code) as (
  values 
  ('a value'), ('09'), ('12'), ('20'), ('007'), 
  ('200'), ('92'), ('11'), ('Foo'), ('Exp3')
) 
select string_agg(ligne_code, ',' order by case when ligne_code ~'^[0-9]+$' then ligne_code::int end, ligne_code)
from data;

returns:

007,09,11,12,20,92,200,a value,Exp3,Foo

The expression when ligne_code ~'^[0-9]+$' checks if ligne_code consists of numbers. If that is the case it's converted to an integer which is the first sort criteria. All non-numbers come after that and are sorted in a alphabetic way.