Migrate Oracle CONNECT BY to postgresql 10

Question

i have this SQL:

SELECT count(*) as ct
          FROM classifications cls
WHERE
   cls.classification_id = :classification_id
START WITH cls.classification_id = :root_classification_id
CONNECT BY NOCYCLE PRIOR cls.classification_id = cls.parent_id

and need to migrate it to postgresql 10.

I have already installed the extension tablefunc and tried with connectedby. here my try:

SELECT count(*) as ct
          FROM classifications cls
WHERE
   cls.classification_id = :classification_id

union
                  SELECT count(classification_id) FROM connectby('classifications','classification_id','parent_id',:root_classification_id,5)
                  as t(classification_id varchar, parent_id varchar,level int)

Problem is that the union is the wrong way because then you get 2 results of the count.

user330315 · Accepted Answer · 2019-10-18 07:42:45Z

2

No need to use the tablefunc extension. This can easily be done using a recursive CTE

with recursive tree as (

  select cls.classification_id, cls.parent_id 
  from classifications cls
  where cls.classification_id = :root_classification_id

  union all

  select child.classification_id, child.parent_id
  from classifications child
     join tree parent on child.parent_id = parent.classification_id
)
select count(*)
from tree;

The first query inside the CTE matches the start with part from Oracle's start with. And the JOIN back to the CTE in the second query matches the connect by part in the Oracle query.

answered Oct 18, 2019 at 7:42

user330315

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

4 Comments

Panagiotis Kanavos Over a year ago

Worth noting that this works in most RDBMSs with few modifications. I wonder if ltree is a good choice too? It's an additional module but results in far better performance.

user330315 Over a year ago

@PanagiotisKanavos: I have never worked with the ltree extension, but recursive queries are quite efficient in Postgres (I have used them with trees containing thousands of rows out of tables with millions of rows without any problems - proper indexes assumed)

Panagiotis Kanavos Over a year ago

Not as efficient as a single range query over an index. LTree is like SQL Server's hierarchyid - a binary path encoding roughly similar to ID1\ID2 etc which means searching for children is essentially a range query similar to like 'ID1\%. Searching for parents, just looking for those IDs. Nested sets are also fast but far harder to maintain, and they need manual implementation anyway

user330315 Over a year ago

@PanagiotisKanavos: if the current structure in Oracle performed good enough, I am sure this query will be good enough in Postgres as well.

Collectives™ on Stack Overflow

Migrate Oracle CONNECT BY to postgresql 10

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related