Results 1 to 2 of 2
  1. #1
    Join Date
    May 2017
    Posts
    1

    Answered: How can i convert this sqlserver query into postgres

    I have this query in sqlserver with 2 tables(one contains a list of companies, the second contains a list of publishers)

    with edges as (
    select t1.company as node1, t2.company as node2
    from table1 t1 join
    table1 t2
    on t1.publisher = t2.publisher
    ),
    cte as (
    select e.node1, e.node2,
    cast('|'+e.node1+'|'+e.node2+'|' as varchar(max)) as nodes,
    1 as level
    from edges e
    union all
    select c.node1, e.node2,
    c.nodes+e.node2+'|',
    1+c.level
    from cte c join
    edges e
    on c.node2 = e.node1 and
    c.nodes not like '|%'+e.node2+'%|'
    ),
    nodes as (
    select node1,
    (case when min(node2) < node1 then min(node2) else node1 end
    ) as grp
    from cte
    group by node1
    )
    select t.company, t.publisher, grp.GroupId
    from table1 t join
    (select n.node1, dense_rank() over (order by grp) as GroupId
    from nodes n
    ) grp
    on t.company = grp.node1;



    I should convert this sqlserver query into postgres

  2. Best Answer
    Posted by shammat

    "You need to use the recursive keyword and use the standard SQL concatenation operator ||:

    Code:
    with recursive edges as (
      select t1.company as node1, t2.company as node2
      from table1 t1 join
      table1 t2
      on t1.publisher = t2.publisher
    ), cte as (
      select e.node1, e.node2,
             '|'||e.node1||'|'||e.node2||'|' as nodes,
             1 as level
      from edges e
      union all
      select c.node1, e.node2,
             c.nodes||e.node2||'|',
             1+c.level
      from cte c 
        join edges e on c.node2 = e.node1 and
      c.nodes not like '|%'||e.node2||'%|'
    ), nodes as (
      select node1,
             case when min(node2) < node1 then min(node2) else node1 end as grp
      from cte
      group by node1
    )
    select t.company, t.publisher, grp.GroupId
    from table1 t 
    join (
      select n.node1, dense_rank() over (order by grp) as GroupId
      from nodes n
    ) grp on t.company = grp.node1;
    A more efficient way would be to use an array to collect the visited nodes, rather then string concatenation:

    Code:
    with recursive edges as (
      select t1.company as node1, t2.company as node2
      from table1 t1 
        join table1 t2 on t1.publisher = t2.publisher
    ), cte as (
      select e.node1, e.node2,
             array[e.node1, e.node2] as nodes,
             1 as level
      from edges e
      union all
      select c.node1, e.node2, c.nodes || e.node2, 
             1 + c.level
      from cte c 
        join edges e on c.node2 = e.node1 
                    and e.node2 <> all(c.nodes)
    ), nodes as (
      select node1,
             case when min(node2) < node1 then min(node2) else node1 end as grp
      from cte
      group by node1
    )
    select t.company, t.publisher, grp.GroupId
    from table1 t 
    join (
      select n.node1, dense_rank() over (order by grp) as GroupId
      from nodes n
    ) grp on t.company = grp.node1;
    "


  3. #2
    Join Date
    Nov 2003
    Posts
    2,988
    Provided Answers: 23
    You need to use the recursive keyword and use the standard SQL concatenation operator ||:

    Code:
    with recursive edges as (
      select t1.company as node1, t2.company as node2
      from table1 t1 join
      table1 t2
      on t1.publisher = t2.publisher
    ), cte as (
      select e.node1, e.node2,
             '|'||e.node1||'|'||e.node2||'|' as nodes,
             1 as level
      from edges e
      union all
      select c.node1, e.node2,
             c.nodes||e.node2||'|',
             1+c.level
      from cte c 
        join edges e on c.node2 = e.node1 and
      c.nodes not like '|%'||e.node2||'%|'
    ), nodes as (
      select node1,
             case when min(node2) < node1 then min(node2) else node1 end as grp
      from cte
      group by node1
    )
    select t.company, t.publisher, grp.GroupId
    from table1 t 
    join (
      select n.node1, dense_rank() over (order by grp) as GroupId
      from nodes n
    ) grp on t.company = grp.node1;
    A more efficient way would be to use an array to collect the visited nodes, rather then string concatenation:

    Code:
    with recursive edges as (
      select t1.company as node1, t2.company as node2
      from table1 t1 
        join table1 t2 on t1.publisher = t2.publisher
    ), cte as (
      select e.node1, e.node2,
             array[e.node1, e.node2] as nodes,
             1 as level
      from edges e
      union all
      select c.node1, e.node2, c.nodes || e.node2, 
             1 + c.level
      from cte c 
        join edges e on c.node2 = e.node1 
                    and e.node2 <> all(c.nodes)
    ), nodes as (
      select node1,
             case when min(node2) < node1 then min(node2) else node1 end as grp
      from cte
      group by node1
    )
    select t.company, t.publisher, grp.GroupId
    from table1 t 
    join (
      select n.node1, dense_rank() over (order by grp) as GroupId
      from nodes n
    ) grp on t.company = grp.node1;
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •