ORDER siblings BY and rownum while migrating from Oracle to PostgreSQL

Question

I am migrating one project from Oracle to PG and have a problem with one sql query.

This is my table:

CREATE TABLE descriptor_value (
    descriptor_value_id bigint NOT NULL,
    descriptor_group_id bigint NOT NULL,
    full_value varchar(4000) NOT NULL,
    short_value varchar(250),
    value_code varchar(30),
    sort_order bigint NOT NULL,
    parent_value_id bigint,
    deleted smallint NOT NULL DEFAULT 0,
    portal smallint NOT NULL DEFAULT 0
) ;

This is the original oracle query that I am migrating:

select rownum as ROW_NUM, DESCRIPTOR_VALUE_ID 
         from DESCRIPTOR_VALUE
         connect by prior DESCRIPTOR_VALUE_ID = PARENT_VALUE_ID
               start with PARENT_VALUE_ID is null
                      and DESCRIPTOR_GROUP_ID = (select DESCRIPTOR_GROUP_ID
                                                   from DESCRIPTOR_VALUE
                                                  where DESCRIPTOR_VALUE_ID = 867)
               order siblings by SORT_ORDER;

And this is my postgresql query:

WITH RECURSIVE descriptor_values AS (
            SELECT
                    ARRAY[descriptor_value_id] AS hierarchy,
                    descriptor_value_id,
                    parent_value_id,
                    sort_order
            FROM
                    descriptor_value
            WHERE
                    parent_value_id IS NULL AND descriptor_group_id = (
                        SELECT descriptor_group_id
                        FROM descriptor_value
                        WHERE descriptor_value_id = 867)
            UNION ALL
            SELECT
                    descriptor_values.hierarchy || dv.descriptor_value_id,
                    dv.descriptor_value_id,
                    dv.parent_value_id,
                    dv.sort_order
            FROM
                    descriptor_value dv
            JOIN descriptor_values ON dv.parent_value_id = descriptor_values.descriptor_value_id

    ) SELECT
            descriptor_value_id
    FROM
            descriptor_values order by hierarchy;

Solution for order siblings by I took from here https://stackoverflow.com/a/17737560/5182503 . However, the order of the rows in postgresql result set differs from the order in oracle result set. What about rownum here I absolutely stopped. Could anyone help me to build right pg query?

Mike Organek · Accepted Answer · 2020-06-29T19:06:49.390

The solution you link assumes there is a natural ordering of the descriptor_value_id.

If the sort_order is unique on the table, then the below query should work.

If not, then we will have to do some monkey business in the values you put into the hierarchy array.

WITH RECURSIVE descriptor_values AS (
            SELECT
                    ARRAY[sort_order] AS hierarchy,
                    descriptor_value_id,
                    parent_value_id,
                    sort_order
            FROM
                    descriptor_value
            WHERE
                    parent_value_id IS NULL AND descriptor_group_id = (
                        SELECT descriptor_group_id
                        FROM descriptor_value
                        WHERE descriptor_value_id = 867)
            UNION ALL
            SELECT
                    descriptor_values.hierarchy || dv.sort_order,
                    dv.descriptor_value_id,
                    dv.parent_value_id,
                    dv.sort_order
            FROM
                    descriptor_value dv
            JOIN descriptor_values ON dv.parent_value_id = descriptor_values.descriptor_value_id

    ) SELECT
            descriptor_value_id
    FROM
            descriptor_values
    order by hierarchy;

Update in response to comment

Since sort_order is not unique across the table, it needs to be incorporated into the hierarchy array. I also added the row_number() to the final query.

WITH RECURSIVE descriptor_values AS (
            SELECT
                    ARRAY[sort_order, descriptor_value_id] AS hierarchy,
                    descriptor_value_id,
                    parent_value_id,
                    sort_order
            FROM
                    descriptor_value
            WHERE
                    parent_value_id IS NULL AND descriptor_group_id = (
                        SELECT descriptor_group_id
                        FROM descriptor_value
                        WHERE descriptor_value_id = 867)
            UNION ALL
            SELECT
                    descriptor_values.hierarchy 
                       || array[dv.sort_order, dv.descriptor_value_id]
                    dv.descriptor_value_id,
                    dv.parent_value_id,
                    dv.sort_order
            FROM
                    descriptor_value dv
            JOIN descriptor_values ON dv.parent_value_id = descriptor_values.descriptor_value_id

    ) SELECT
            row_number() over (order by hierarchy), 
            hierarchy,
            descriptor_value_id
    FROM
            descriptor_values 
   order by hierarchy;

@Shtirlits I updated my answer. Please try the new query and let me know if it works. — Mike Organek, Jun 29 '20 at 18:48
Thank you very much. I edited this line `descriptor_values.hierarchy || array[dv.sort_order, dv.descriptor_value_id],`. However, the result in oracle differs from the result on pg. Does you solution suppose that sort_order is inside one parent? For example, if we have parent A that has two children B and C, then childB has sort_order=0 and childC has sort_order=1. — ThatsMe, Jun 29 '20 at 19:00
@Shtirlits It is supposed to account for the sort order. Try adding the `hierarchy` column to your output to see what the query does. Does the hierarchy in your table have more than one root element? If so, I added `sort_order` to the initial array. — Mike Organek, Jun 29 '20 at 19:07
Thank you very much. You last solution gives the same results as in oracle. — ThatsMe, Jun 29 '20 at 19:14

ORDER siblings BY and rownum while migrating from Oracle to PostgreSQL

1 Answers1