Groups > comp.databases.postgresql > #823

Re: Postgres 9.6 - ltree extension - (re)build a tree on a table

X-Received	by 2002:ac8:4452:: with SMTP id m18-v6mr2474232qtn.5.1524174310412; Thu, 19 Apr 2018 14:45:10 -0700 (PDT)
X-Received	by 10.31.150.196 with SMTP id y187mr907179vkd.4.1524174310083; Thu, 19 Apr 2018 14:45:10 -0700 (PDT)
Path	csiph.com!feeder.erje.net!2.us.feeder.erje.net!feeder.usenetexpress.com!feeder-in1.iad1.usenetexpress.com!peer03.iad!feed-me.highwinds-media.com!news.highwinds-media.com!f34-v6no7170215qtb.0!news-out.google.com!c8-v6ni5833qtc.0!nntp.google.com!f34-v6no7170209qtb.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.postgresql
Date	Thu, 19 Apr 2018 14:45:09 -0700 (PDT)
In-Reply-To	<m2lgdl34h7.fsf@tapoueh.org>
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=80.116.47.113; posting-account=eAqIKAoAAAD_lj433Rbn4ehhnkebGygk
NNTP-Posting-Host	80.116.47.113
References	<f1e640c3-d6a7-4a19-af0e-1a7dfa54d65d@googlegroups.com> <m2lgdl34h7.fsf@tapoueh.org>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<3d6183b3-6fbf-442f-90bf-d2530eb6cc2d@googlegroups.com> (permalink)
Subject	Re: Postgres 9.6 - ltree extension - (re)build a tree on a table
From	lordluke80@gmail.com
Injection-Date	Thu, 19 Apr 2018 21:45:10 +0000
Content-Type	text/plain; charset="UTF-8"
Content-Transfer-Encoding	quoted-printable
X-Received-Bytes	2074
X-Received-Body-CRC	3882524449
Xref	csiph.com comp.databases.postgresql:823

Show key headers only | View raw

Il giorno martedì 17 aprile 2018 23:31:01 UTC+2, Dimitri Fontaine ha scritto:
> lordluke80@gmail.com writes:
> > Any suggestion to achieve that or snippets about how to do that? havent foud any valid example for 'rebuild a tree in postgres' in the official docs.
> 
> Have a look at WITH RECURSIVE:
> 
>   https://www.postgresql.org/docs/current/static/queries-with.html
>   https://tapoueh.org/blog/2018/01/exporting-a-hierarchy-in-json-with-recursive-queries/
>   
> Regards,
> -- 
> Dimitri Fontaine

Thanks Dimitri (and congrats for the book!)

I modified the table and this solution came up:

http://dpaste.com/2QXFP0M

What I'm trying to do is fetchig for each id the one with max level.

However, I'm messing up something since despite using DISTINCT there are still duplicated rows.

Any hints about that?

Thanks

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Find similar

Thread

Postgres 9.6 - ltree extension - (re)build a tree on a table lordluke80@gmail.com - 2018-04-17 01:44 -0700
  Re: Postgres 9.6 - ltree extension - (re)build a tree on a table Dimitri Fontaine <dim@tapoueh.org> - 2018-04-17 23:31 +0200
    Re: Postgres 9.6 - ltree extension - (re)build a tree on a table lordluke80@gmail.com - 2018-04-19 14:45 -0700

csiph-web