Question 89757 in PostgreSQL Question posted in
The official documentation can be found here.

Restrict string_agg order by in postgres – Postgresql

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search