Thread: Tree structure
<p><font size="2">Anybody know how to make a tree structure using related tables using Postgres. Something like a directorystructure is what I'm aiming for. I'm sure there is an easy way but I'm having probs.</font><p><font size="2">Anyhelp would be appreciated.</font><p><font size="2">Ben.</font>
Normally it's done with self-relation. You need a table with "ID" and "PARENT_ID". PARENT_ID will contain null for rootlevel and other row's ID for children. One warning: going through such tree could be slow. I don't know is there anythingPostgreSQL specific that might help. I know that Oracle have a special tree command.<br /><br /> At 14:27 11.9.2000, Trewern, Ben wrote: <br /><br /><font size="2"><blockquote cite="cite" type="cite">Anybody know how to make atree structure using related tables using Postgres. Something like a directory structure is what I'm aiming for. I'm surethere is an easy way but I'm having probs.<br /></blockquote></font><br /> Any help would be appreciated. <br /><br /><fontsize="2">Ben.</font><br />
Thanks for your offer, I'll be very interested to see how you did that. I also found very interesting solution at http://www.dbmsmag.com/9603d06.html http://www.dbmsmag.com/9604d06.html http://www.dbmsmag.com/9605d06.html At 11:22 12.9.2000 , kovacsz wrote: >We are using tree structures (and partially ordered trees) with >PostgreSQL. After the beginning success we got into speed problems. With >special plus tables, triggers and functions now we have a reasonably fast >database which contains about 5-10 tree structures. If you are interested, >I may send you some of these part of our database. But, PostgreSQL "from >base" doesn't support tree structures, neither recursions (like in SQL3), >as far as I know (see TODO list).
<p><font size="2">Any details you can supply would be appreciated. I have got a table at the moment as:</font><p><font size="2"> id| group_name | parentid</font><br /><font size="2">-------------------------------</font><br /><font size="2"> 1 | Main |</font><br /><font size="2"> 2 | Group |</font><br /><font size="2"> 3 | User |</font><br /><font size="2"> 4 | Group1 | 1</font><br /><font size="2"> 5 | Group2 | 1</font><br/><font size="2"> 6 | Group2-1 | 5</font><p><font size="2">Just for testing. I can see how it should workbut the client side code is defeating me at the moment. I'm working in delphi with winzeos (cracking components by theway). Anyone done this.</font><p><font size="2">Thanks for any help</font><p><font size="2">Ben Trewern</font><p><fontsize="2">> -----Original Message-----</font><br /><font size="2">> From: kovacsz [<a href="mailto:zoli@pc10.radnoti-szeged.sulinet.hu">mailto:zoli@pc10.radnoti-szeged.sulinet.hu</a>]</font><br/><font size="2">>Sent: 12 September 2000 10:23</font><br /><font size="2">> To: Zeljko Trogrlic</font><br /><font size="2">>Cc: Trewern, Ben; 'pgsql-sql@postgresql.org'</font><br /><font size="2">> Subject: Re: [SQL] Tree structure</font><br/><font size="2">> </font><br /><font size="2">> </font><br /><font size="2">> We are using treestructures (and partially ordered trees) with</font><br /><font size="2">> PostgreSQL. After the beginning successwe got into speed </font><br /><font size="2">> problems. With</font><br /><font size="2">> special plus tables,triggers and functions now we have a </font><br /><font size="2">> reasonably fast</font><br /><font size="2">>database which contains about 5-10 tree structures. If you </font><br /><font size="2">> are interested,</font><br/><font size="2">> I may send you some of these part of our database. But, </font><br /><font size="2">>PostgreSQL "from </font><br /><font size="2">> base" doesn't support tree structures, neither recursions</font><br /><font size="2">> (like in SQL3), </font><br /><font size="2">> as far as I know (see TODO list).</font><br/><font size="2">> </font>
On Mon, Sep 11, 2000 at 01:27:48PM +0100, Trewern, Ben wrote: > Anybody know how to make a tree structure using related tables using > Postgres. Something like a directory structure is what I'm aiming for. I'm > sure there is an easy way but I'm having probs. I am not quite sure whether this is what you are thinking about: create table tree ( id int4, parentid int4, data text) A structure like a|__b|__c can be achieved by insert into tree (id, parentid, data) values (1, NULL, 'a'); insert into tree (id, parentid, data) values (2, 1, 'b'); insert into tree (id, parentid, data) values (3, 1, 'c'); Probably you'll also want to use a sequence for the ids, and to declare indices on id, parentid etc. > > Any help would be appreciated. > > Ben. -- -------------------------------------------------------------------------- Albert Reiner <areiner@tph.tuwien.ac.at> Deutsch * English * Esperanto * Latine --------------------------------------------------------------------------
Hi, there I hava a very odd problem, I have table named categories, it's been used for a long time, nobody change anything on it, I have a script to read it out every day, however, I got Permission deny, those script has been used for a long time. When I want to check the permission of the table by urldb=# \d categories Table "categories" Attribute | Type | Modifier -------------+-------------+----------cid | integer | not nullname | varchar(32) |code | varchar(10)|description | text |super | integer | Index: categories_pkey urldb=# \z categories ERROR: aclitemout: bad ai_idtype: 53 What this message means? -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.ipinc.com