Re: [GENERAL] "Reverse" inheritance?
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 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.johns...@gmail.com> wrote: On Mon, Apr 3, 2017 at 7:07 PM, Tim Uckun 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "Reverse" inheritance?
>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. If can say create table ubuntu_17_04 inherits ubuntu and have it return all the parents data as I described it would be awesome >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. Yes this might be a problem but one I could easily overcome. >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. On Tue, Apr 4, 2017 at 6:43 PM, vinny 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.johns...@gmail.com> wrote: >> >> On Mon, Apr 3, 2017 at 7:07 PM, Tim Uckun 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 >>> >>> I haven't had a chance to leverage it myself but the concept it embodies >>> is solid. >>> >>> David J. >>> >>> >>>
Re: [GENERAL] "Reverse" inheritance?
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.johns...@gmail.com> wrote: On Mon, Apr 3, 2017 at 7:07 PM, Tim Uckun 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 I haven't had a chance to leverage it myself but the concept it embodies is solid. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "Reverse" inheritance?
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.johns...@gmail.com> wrote: > On Mon, Apr 3, 2017 at 7:07 PM, Tim Uckun 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 > > I haven't had a chance to leverage it myself but the concept it embodies > is solid. > > David J. > >
Re: [GENERAL] "Reverse" inheritance?
On Mon, Apr 3, 2017 at 7:07 PM, Tim Uckun 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 I haven't had a chance to leverage it myself but the concept it embodies is solid. David J.
[GENERAL] "Reverse" inheritance?
I am trying to make postgres tables work like an object hierarchy. As an example I have done this. drop table if exists os.linux cascade; create table os.linux ( script_name character varying(255) NOT NULL, script_body text, CONSTRAINT os_linux_pkey PRIMARY KEY (script_name) ); drop table if exists os.red_hat; CREATE TABLE os.red_hat ( CONSTRAINT os_red_hat_pkey PRIMARY KEY (script_name) )INHERITS (os.linux); drop table if exists os.debian; CREATE TABLE os.debian ( CONSTRAINT os_debian_pkey PRIMARY KEY (script_name) )INHERITS (os.linux); insert into os.linux(script_name, script_body) VALUES ('package', 'tgz' ); insert into os.linux(script_name, script_body) VALUES ('awk', 'awk' ); insert into os.debian(script_name, script_body) values( 'package', 'apt'); insert into os.red_hat(script_name, script_body) values( 'package', 'yum'); When I do SELECT * from os.debian I would like to get two records one where the package is 'apt' and one where the awk is 'awk'. So the package row is overridden in the child but it inherits the parent row. Is there a way to do this? Ideally I would like to have a deeper hierarchy like nix -> linux -> debian -> ubuntu -> ubuntu_16_04 so that when I select from ubuntu_16_04 I get all the rows from all the parent tables but properly overridden so they don't union. Thanks.