Restrict string_agg order by in postgres

Question

While working with postgres db, I came across a situation where I will have to display column names based on their ids stored in a table with comma separated. Here is a sample:

table1 name: labelprint

id field_id
1  1,2

table2 name: datafields

id field_name
1  Age
2  Name
3  Sex

Now in order to display the field name by picking ids from table1 i.e. 1,2 from field_id column, I want the field_name to be displayed in same order as their respective ids as

Expected result:

id field_id field_name
1  2,1      Name,Age

To achieve the above result, I have written the following query:

select l.id,l.field_id ,string_agg(d.field_name,',') as field_names
from labelprint l
join datafields d on d.id = ANY(string_to_array(l.field_id::text,','))
group by l.id
order by l.id

However, the string_agg() functions sort the final string in ascending order and displays the output as shown below:

id field_id field_name
1  2,1      Age, Name

As you can see the order is not maintained in the field_name column which I want to display as per field_id value order.

Any suggestion/help is highly appreciated.

Thanks in advance!

Already mentioned in the description.

Change your data model: Never store multiple values in a single column, it will always cause problems. — Frank Heikens, Feb 14 '23 at 13:09

score 1 · Accepted Answer · answered Feb 14 '23 at 14:53

While this will probably be horrible for performance, as well as readability and maintainability, you can dynamically compute the order you want:

select l.id,l.field_id,
  string_agg(d.field_name,',' 
    order by array_position(string_to_array(l.field_id::text,','),d.id)
  ) as field_names
from labelprint l
join datafields d on d.id = ANY(string_to_array(l.field_id::text,','))
group by l.id
order by l.id;

You should at least store your array as an actual array, not as a comma delimited string. Or maybe use an intermediate table and don't store arrays at all.

Great buddy...working as needed! – Shantilal Suthar Feb 15 '23 at 06:36 — Shantilal Suthar, Feb 15 '23 at 06:36

SelVazi · Answer 2 · 2023-02-14T15:54:49.397

-1

With a small modification to your existing query you could do it as follows :

select l.id, l.field_id, string_agg(d.field_name,',') as field_names
from labelprint l
join datafields d on d.id::varchar = ANY(string_to_array(l.field_id,','))
group by l.id, l.field_id
order by l.id

Demo here

edited Feb 14 '23 at 15:54

answered Feb 14 '23 at 13:29

SelVazi

10,028
2
13
29

It doesn't work, gives same answer as the other query. – jjanes Feb 14 '23 at 14:41
@jjanes Have a look on the demo – SelVazi Feb 14 '23 at 15:55
This is not working for me too. Had a look on demo as well. – Shantilal Suthar Feb 15 '23 at 05:43
Weird .. Can you share the error that you have got ? and your PostgreSql version ? – SelVazi Feb 15 '23 at 07:19
1

You need to add another row with the other ordering, or else you can get the right answer purely by luck. https://www.db-fiddle.com/f/vipZ8YLRPQfY1YL31EV7fu/2 – jjanes Feb 15 '23 at 17:27

Restrict string_agg order by in postgres

2 Answers2