PostgreSQL order in recursive query

Question

I try to get result of my query in natural order, but have a fail.

create table Tab2 (id int, F1 varchar(100));
insert into Tab2 values(1, '10,56,657,34,767,71');
insert into Tab2 values(3, '1,5487,27,9');
insert into Tab2 values(4, '11,13,37,2');
insert into Tab2 values(2, '12,6,65,8,67,22,70,5');


WITH RECURSIVE etc (id,  DataItem, F1)  AS (
                (SELECT id,
                LEFT(F1, strpos(concat(F1, ','), ',')-1) AS Part,
                overlay(F1 placing '' from 1 for strpos(concat(F1, ','),',')) AS Remainder
                FROM Tab2
                --ORDER BY Remainder
                )
    
 UNION ALL

                (SELECT id,
                LEFT(F1, strpos(concat(F1, ','), ',')-1),
                overlay(F1 placing '' from 1 for strpos(concat(F1, ','),','))
                FROM etc e
                WHERE F1 > ''
                --ORDER BY Dataitem
                 )
    )

SELECT id, row_number() over(partition BY id ORDER BY id) num, DataItem from etc ORDER BY id;

http://sqlfiddle.com/#!15/b0ccc6/89/0

Where is my mistake?

Storing comma separated values in a single column is a big mistake to begin with. But you need to tell us which order you actually want — user330315
– user330315, Commented Jul 16, 2020 at 11:10
@MaciejLos Yes! Its great! My respect for you! I thought about one additional column, but try to solve a problem with ORDER BY! I am deeply grateful to you!!! — Alexander
– Alexander, Commented Jul 16, 2020 at 11:34

user330315 · Accepted Answer · 2020-07-16 11:14:52Z

1

If I understand your query correctly you are trying to get all elements from your (badly designed) comma separated string. There is no need to use a recursive query for that.

You can convert the string to array which can then be "unnested" into rows. Using the option with ordinality will also return the index of each element in the array which can be used in an order by to preserve the original order of the items in the string.

select t2.id, i.num, i.dataitem
from tab2 t2
  cross join unnest(string_to_array(f1,',')) with ordinality as i(dataitem, num)
order by t2.id, i.num;

Online example

answered Jul 16, 2020 at 11:14

user330315

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

2 Comments

Maciej Los Over a year ago

Excellent! I've never used unnest function before. This is very interesting example of using it. Thanks for sharing it!

Alexander Over a year ago

Thanks for your attention for my question. I understand this decision. And You are right with "badly designed" db, but it was a test task in CTE. And it was a new subject for me, but i solved it without recursion as SELECT id, row_number() over(partition BY id ORDER BY id) num, regexp_split_to_table(F1, ',') FROM Tab2

Maciej Los · Accepted Answer · 2020-07-16 11:28:23Z

0

Assuming that you want to get DataItem in order, in which it is placed in comma separated string, you can use another field to get an "index" (in below example it's a rowno).

For example:

id, dataitem
1, 10
1, 56
1, 657
...
1, 71
2, 12
...
2, 5
etc.

See:

WITH RECURSIVE etc (id, rowno,  DataItem, F1)  AS (
                (SELECT id, 1 as rowno,
                LEFT(F1, strpos(concat(F1, ','), ',')-1) AS Part,
                overlay(F1 placing '' from 1 for strpos(concat(F1, ','),',')) AS Remainder
                FROM Tab2
                )
    
 UNION ALL

                (SELECT id, rowno +1 as rowno,
                LEFT(F1, strpos(concat(F1, ','), ',')-1),
                overlay(F1 placing '' from 1 for strpos(concat(F1, ','),','))
                FROM etc e
                WHERE F1 > ''
                 )
    )

SELECT id, DataItem
from etc
ORDER BY id, RowNo;

SqlFiddle (after changes)

edited Jul 16, 2020 at 11:28

answered Jul 16, 2020 at 11:19

Maciej Los

8,6211 gold badge22 silver badges36 bronze badges

2 Comments

Alexander Over a year ago

Yes! Its great! My respect for you! I thought about one additional column, but try to solve a problem with ORDER BY! I am deeply grateful to you!!!

Alexander Over a year ago

i solved it without recursion earlier <pre>SELECT id, row_number() over(partition BY id ORDER BY id) num, regexp_split_to_table(F1, ',') FROM Tab2 <code>

Collectives™ on Stack Overflow

PostgreSQL order in recursive query

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related