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?
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;