Re: "Reverse" inheritance? - Mailing list pgsql-general

On 2017-04-04 09:12, Tim Uckun wrote:
>> I agree with the barking up the wrong tree, building a physical tree
> in tables doesn't sound right
> given that you will have to create a new branch in the tree when a new
> version/variation of ubuntu comes out.
>
> This doesn't bother me that much.

It should. You are using tables as data, which is pretty much always a
smell of bad design.
I could be pedantic and ask how you would store unix version "14.5
\%funky penguin%/ rev 1,5"
given that most of that name consists of characters that are not allowed
in a table name.

>
>> Also think about how you are going to do basic queries like listing
> all known unix variants; if that is hidden in the table namesthen
> you'll have to issue DDL queries to do the work of SELECT queries,
> which just sounds wrong to me.
>
> Yes this might be a problem but one I could easily overcome.

Sure, but why would you though? You already have everything in place for
creating records,
why bother creating a different system just for the unix versions?


>
>> I'd go for a tree, possibly using recursive CTE's to dig it.
>
> I was thinking a window function but yea I am sure there is a way to
> do it with a flat table.

I'm not sure you can do it with windowing actually,
given that you'd have to sort every record based on a match with the
previous record.
But I've never tried it because CTE's make it so easy :-)

>
> On Tue, Apr 4, 2017 at 6:43 PM, vinny <vinny@xs4all.nl> wrote:
>
>> I agree with the barking up the wrong tree, building a physical tree
>> in tables doesn't sound right
>> given that you will have to create a new branch in the tree when a
>> new version/variation of ubuntu comes out.
>>
>> Also think about how you are going to do basic queries like listing
>> all known unix variants; if that is hidden in the table names
>> then you'll have to issue DDL queries to do the work of SELECT
>> queries, which just sounds wrong to me.
>>
>> I'd go for a tree, possibly using recursive CTE's to dig it.
>>
>> On 2017-04-04 05:19, Tim Uckun wrote:
>> I have thought of doing something like a single table inheritance
>> and it
>> could be done but I thought this might be a little more elegant.
>>
>> On Tue, Apr 4, 2017 at 2:15 PM, David G. Johnston <
>> david.g.johnston@gmail.com> wrote:
>>
>> On Mon, Apr 3, 2017 at 7:07 PM, Tim Uckun <timuckun@gmail.com>
>> wrote:
>>
>> I am trying to make postgres tables work like an object hierarchy.
>> As an
>> example I have done this.
>>
>> ​I suspect you are barking up the wrong tree ;)
>>
>> You are probably better off incorporating something like the "ltree"
>> type
>> to encode the taxonomy.
>>
>> https://www.postgresql.org/docs/current/static/ltree.html [1]
>>
>> I haven't had a chance to leverage it myself but the concept it
>> embodies
>> is solid.
>>
>> David J.
>> ​
>
>
>
> Links:
> ------
> [1] https://www.postgresql.org/docs/current/static/ltree.html