Thread: Type inheritance
Hi all,<br /> I just want to ask you if it's possible to define sub types in PostgreSQL (Type inheritance). I found thattable inheritance is possible...but I'd like to do it on types.<br /><br /> Example:<br /> define a type Value and twosubtypes Temperature and Humidity and then define a table that has an attribute of Value type (that can accept both specializedtypes).<br /><br /> Thanks
Gianvito Pio <pio.gianvito@gmail.com> writes:
> I just want to ask you if it's possible to define sub types in PostgreSQL
> (Type inheritance). I found that table inheritance is possible...but I'd
> like to do it on types.
It's fairly unclear what your actual requirements are here, but perhaps
domains would do the job?
regards, tom lane
Domains just allow me to redefine base types, including some restrictions. I just want to define a my own type and then to define some new types that inherit from the first one. I hope it's clear now...
2009/6/5 Tom Lane <tgl@sss.pgh.pa.us>
Gianvito Pio <pio.gianvito@gmail.com> writes:It's fairly unclear what your actual requirements are here, but perhaps
> I just want to ask you if it's possible to define sub types in PostgreSQL
> (Type inheritance). I found that table inheritance is possible...but I'd
> like to do it on types.
domains would do the job?
regards, tom lane
I'm not sure how Temperature and Humidity are related as types beyond "extends float". i.e. What tuple definition could alternatively have a Temp or a Humidity value? (I do understand their physical relationship :) ) Gianvito Pio wrote: > Hi all, > I just want to ask you if it's possible to define sub types in > PostgreSQL (Type inheritance). I found that table inheritance is > possible...but I'd like to do it on types. > > Example: > define a type Value and two subtypes Temperature and Humidity and then > define a table that has an attribute of Value type (that can accept > both specialized types). > > Thanks
Oh that was just an example...Let me make a real example:
I have a table "Sensors" (that has to contain different sensor types) with a Value. That value doesn't have to be fixed, but I want to define it in a way that it changes its structure when the sensor type changes. For example, for Temperature sensor I would like to have just a float number, but for the humidity I could need of absolute and relative humidity (2 floats). I'd just like to know if I can say that the VALUE is a composite type...that I have then to specialize in Temp Type and Humidity type...using them in their particular case.
This procedure has a name...and it's the inheritance. As I understood, postgres allow me the table inheritance, but not the type inerithance. Here is an example about how this works on Oracle: http://www.oracle.com/technology/products/oracle9i/daily/jan29.html
2009/6/5 Rob Sargent <robjsargent@gmail.com>
I'm not sure how Temperature and Humidity are related as types beyond "extends float". i.e. What tuple definition could alternatively have a Temp or a Humidity value? (I do understand their physical relationship :) )--
Gianvito Pio wrote:Hi all,
I just want to ask you if it's possible to define sub types in PostgreSQL (Type inheritance). I found that table inheritance is possible...but I'd like to do it on types.
Example:
define a type Value and two subtypes Temperature and Humidity and then define a table that has an attribute of Value type (that can accept both specialized types).
Thanks
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
On 2009-06-06, Gianvito Pio <pio.gianvito@gmail.com> wrote: > --001485f44bfe3e357c046ba8b3d6 > Content-Type: text/plain; charset=ISO-8859-1 > Content-Transfer-Encoding: 7bit > > Oh that was just an example...Let me make a real example: > I have a table "Sensors" (that has to contain different sensor types) with > a Value. That value doesn't have to be fixed, but I want to define it in a > way that it changes its structure when the sensor type changes. For > example, for Temperature sensor I would like to have just a float number, > but for the humidity I could need of absolute and relative humidity > (2 floats). I'd just like to know if I can say that the VALUE is a composite > type...that I have then to specialize in Temp Type and Humidity type...using > them in their particular case. this appears to be EAV in disguise.
Mhm...maybe. I need that thing for just 1 table... Have you seen the Oracle example? Do you know if I can do a similar thing in PostgreSQL? ----- Original Message ----- From: "Jasen Betts" <jasen@xnet.co.nz> Newsgroups: gmane.comp.db.postgresql.sql To: <pgsql-sql@postgresql.org> Sent: Saturday, June 06, 2009 2:04 PM Subject: Re: [SQL] Type inheritance > > On 2009-06-06, Gianvito Pio <pio.gianvito@gmail.com> wrote: >> --001485f44bfe3e357c046ba8b3d6 >> Content-Type: text/plain; charset=ISO-8859-1 >> Content-Transfer-Encoding: 7bit >> >> Oh that was just an example...Let me make a real example: >> I have a table "Sensors" (that has to contain different sensor types) >> with >> a Value. That value doesn't have to be fixed, but I want to define it in >> a >> way that it changes its structure when the sensor type changes. For >> example, for Temperature sensor I would like to have just a float number, >> but for the humidity I could need of absolute and relative humidity >> (2 floats). I'd just like to know if I can say that the VALUE is a >> composite >> type...that I have then to specialize in Temp Type and Humidity >> type...using >> them in their particular case. > > this appears to be EAV in disguise. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
On Sat, Jun 6, 2009 at 12:10 AM, Gianvito Pio<pio.gianvito@gmail.com> wrote: > That value doesn't have to be fixed, but I want to define it in a > way that it changes its structure when the sensor type changes. For > example, for Temperature sensor I would like to have just a float number, > but for the humidity I could need of absolute and relative humidity > (2 floats). I'd just like to know if I can say that the VALUE is a composite > type...that I have then to specialize in Temp Type and Humidity type...using > them in their particular case. I think that best solution for what you want to achieve is to design you own vertically partitioned table hierarchy. PostgreSQL's table inheritance isn't going to allow that relationships that you may want between the sub-types. Another PostgreSQL specific feature that allows for hierarchical data is the contrib module h-store. Its kind-of like EAV for a column instead of a table. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On Sat, Jun 6, 2009 at 8:30 AM, Richard Broersma <richard.broersma@gmail.com> wrote:
On Sat, Jun 6, 2009 at 12:10 AM, Gianvito Pio<pio.gianvito@gmail.com> wrote:I think that best solution for what you want to achieve is to design
> That value doesn't have to be fixed, but I want to define it in a
> way that it changes its structure when the sensor type changes. For
> example, for Temperature sensor I would like to have just a float number,
> but for the humidity I could need of absolute and relative humidity
> (2 floats). I'd just like to know if I can say that the VALUE is a composite
> type...that I have then to specialize in Temp Type and Humidity type...using
> them in their particular case.
you own vertically partitioned table hierarchy. PostgreSQL's table
inheritance isn't going to allow that relationships that you may want
between the sub-types.
Another PostgreSQL specific feature that allows for hierarchical data
is the contrib module h-store. Its kind-of like EAV for a column
instead of a table.
Are we storing sensor types (name, etc, sensor-report) where the report column has to be polymorphic or are we storing instances of sensor results (sensor-id, sensor-report). For the former a single table with an array of number in the report column might work, for the latter it seems that separate type specific tables extending/dependent to a table of sensors could do the trick. What you're using to access the store might also affect the design.