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
– Frank Heikens, Commented Feb 14, 2023 at 13:09

jjanes · Accepted Answer · 2023-02-14 14:53:46Z

1

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.

answered Feb 14, 2023 at 14:53

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shantilal Suthar Over a year ago

Great buddy...working as needed!

SelVazi · Accepted Answer · 2023-02-14 15:54:49Z

-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, 2023 at 15:54

answered Feb 14, 2023 at 13:29

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

5 Comments

jjanes Over a year ago

It doesn't work, gives same answer as the other query.

SelVazi Over a year ago

@jjanes Have a look on the demo

Shantilal Suthar Over a year ago

This is not working for me too. Had a look on demo as well.

SelVazi Over a year ago

Weird .. Can you share the error that you have got ? and your PostgreSql version ?

jjanes Over a year ago

You need to add another row with the other ordering, or else you can get the right answer purely by luck. db-fiddle.com/f/vipZ8YLRPQfY1YL31EV7fu/2

Collectives™ on Stack Overflow

Restrict string_agg order by in postgres

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related