pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedType Inheritance
13+ messages / 6 participants
[nested] [flat]
* Type Inheritance
@ 2004-11-25 21:47 Andrew Thorley <andrew.thorley@linuxmail.org>
0 siblings, 1 reply; 13+ messages in thread
From: Andrew Thorley @ 2004-11-25 21:47 UTC (permalink / raw)
To: pgsql-sql
Does anyone know how to implement type inheritance in postgresql? in oracle you just use the word UNDER in ur code i.e:
CREATE TYPE test2_UDT UNDER test1_UDT AS (abc INT);
any ideas?
--
______________________________________________
Check out the latest SMS services @ http://www.linuxmail.org
This allows you to send and receive SMS through your mailbox.
Powered by Outblaze
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Type Inheritance
@ 2004-11-26 18:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andrew Thorley <andrew.thorley@linuxmail.org>
0 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2004-11-26 18:14 UTC (permalink / raw)
To: Andrew Thorley <andrew.thorley@linuxmail.org>; +Cc: pgsql-sql
"Andrew Thorley" <andrew.thorley@linuxmail.org> writes:
> Does anyone know how to implement type inheritance in postgresql? in oracle you just use the word UNDER in ur code i.e:
> CREATE TYPE test2_UDT UNDER test1_UDT AS (abc INT);
If you had said what this *does*, we might be better able to help.
But take a look at CREATE DOMAIN, as well as the INHERITS and LIKE
options in CREATE TABLE. Some part of that might be close to what
you are looking for.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Type Inheritance
@ 2004-11-27 14:04 Andrew Thorley <andrew.thorley@linuxmail.org>
0 siblings, 0 replies; 13+ messages in thread
From: Andrew Thorley @ 2004-11-27 14:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
hi tom thanks for reply.
im trying to provide inheritance for data types (domains), not tables. i will look into the syntax associated with domains as data types does not include an inheritance function (or so i think).
regards andy.
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Andrew Thorley" <andrew.thorley@linuxmail.org>
Subject: Re: [SQL] Type Inheritance
Date: Fri, 26 Nov 2004 13:14:07 -0500
>
> "Andrew Thorley" <andrew.thorley@linuxmail.org> writes:
> > Does anyone know how to implement type inheritance in postgresql? in oracle you just use the word UNDER in ur code i.e:
> > CREATE TYPE test2_UDT UNDER test1_UDT AS (abc INT);
>
> If you had said what this *does*, we might be better able to help.
>
> But take a look at CREATE DOMAIN, as well as the INHERITS and LIKE
> options in CREATE TABLE. Some part of that might be close to what
> you are looking for.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
--
______________________________________________
Check out the latest SMS services @ http://www.linuxmail.org
This allows you to send and receive SMS through your mailbox.
Powered by Outblaze
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Type Inheritance
@ 2004-11-27 15:33 Andrew Thorley <andrew.thorley@linuxmail.org>
0 siblings, 0 replies; 13+ messages in thread
From: Andrew Thorley @ 2004-11-27 15:33 UTC (permalink / raw)
To: Andrew Thorley <andrew.thorley@linuxmail.org>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
"If you had said what this *does*, we might be better able to help"
basically i have a series of types (or data types), of which one is the root and each of the other types appear under the root as in generalisation/specialisation style, inheriting the above types attributes etc. this is what im trying to achieve, although at present, to no avail :(
----- Original Message -----
From: "Andrew Thorley" <andrew.thorley@linuxmail.org>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: [SQL] Type Inheritance
Date: Sat, 27 Nov 2004 22:04:55 +0800
>
> hi tom thanks for reply.
>
> im trying to provide inheritance for data types (domains), not tables. i will look into the syntax associated with domains as data types does not include an inheritance function (or so i think).
>
> regards andy.
>
>
> ----- Original Message -----
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
> To: "Andrew Thorley" <andrew.thorley@linuxmail.org>
> Subject: Re: [SQL] Type Inheritance
> Date: Fri, 26 Nov 2004 13:14:07 -0500
>
> >
> > "Andrew Thorley" <andrew.thorley@linuxmail.org> writes:
> > > Does anyone know how to implement type inheritance in postgresql? in oracle you just use the word UNDER in ur code i.e:
> >
> CREATE TYPE test2_UDT UNDER test1_UDT AS (abc INT);
> >
> > If you had said what this *does*, we might be better able to help.
> >
> > But take a look at CREATE DOMAIN, as well as the INHERITS and LIKE
> > options in CREATE TABLE. Some part of that might be close to what
> > you are looking for.
> >
> > regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if your
> > joining column's datatypes do not match
> >
>
> --
> ______________________________________________
> Check out the latest SMS services @ http://www.linuxmail.org
> This allows you to send and receive SMS through your mailbox.
>
>
> Powered by Outblaze
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
--
______________________________________________
Check out the latest SMS services @ http://www.linuxmail.org
This allows you to send and receive SMS through your mailbox.
Powered by Outblaze
^ permalink raw reply [nested|flat] 13+ messages in thread
* Type inheritance
@ 2009-06-05 10:30 Gianvito Pio <pio.gianvito@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Gianvito Pio @ 2009-06-05 10:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Type inheritance
@ 2009-06-05 13:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gianvito Pio <pio.gianvito@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2009-06-05 13:48 UTC (permalink / raw)
To: Gianvito Pio <pio.gianvito@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Type inheritance
@ 2009-06-05 15:13 Gianvito Pio <pio.gianvito@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Gianvito Pio @ 2009-06-05 15:13 UTC (permalink / raw)
To: pgsql-sql
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:
> > 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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Type inheritance
@ 2009-06-05 16:38 Rob Sargent <robjsargent@gmail.com>
parent: Gianvito Pio <pio.gianvito@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Rob Sargent @ 2009-06-05 16:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Type inheritance
@ 2009-06-06 07:10 Gianvito Pio <pio.gianvito@gmail.com>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Gianvito Pio @ 2009-06-06 07:10 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Type inheritance
@ 2009-06-06 12:04 Jasen Betts <jasen@xnet.co.nz>
parent: Gianvito Pio <pio.gianvito@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Jasen Betts @ 2009-06-06 12:04 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Type inheritance
@ 2009-06-06 14:18 Gianvito Pio <pio.gianvito@gmail.com>
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 13+ messages in thread
From: Gianvito Pio @ 2009-06-06 14:18 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Type inheritance
@ 2009-06-06 14:30 Richard Broersma <richard.broersma@gmail.com>
parent: Gianvito Pio <pio.gianvito@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Richard Broersma @ 2009-06-06 14:30 UTC (permalink / raw)
To: Gianvito Pio <pio.gianvito@gmail.com>; +Cc: 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Type inheritance
@ 2009-06-06 15:11 Rob Sargent <robjsargent@gmail.com>
parent: Richard Broersma <richard.broersma@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Rob Sargent @ 2009-06-06 15:11 UTC (permalink / raw)
To: pgsql-sql
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:
>
> > 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.
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2009-06-06 15:11 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-25 21:47 Type Inheritance Andrew Thorley <andrew.thorley@linuxmail.org>
2004-11-26 18:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-27 14:04 Re: Type Inheritance Andrew Thorley <andrew.thorley@linuxmail.org>
2004-11-27 15:33 Re: Type Inheritance Andrew Thorley <andrew.thorley@linuxmail.org>
2009-06-05 10:30 Type inheritance Gianvito Pio <pio.gianvito@gmail.com>
2009-06-05 13:48 ` Re: Type inheritance Tom Lane <tgl@sss.pgh.pa.us>
2009-06-05 15:13 ` Re: Type inheritance Gianvito Pio <pio.gianvito@gmail.com>
2009-06-05 16:38 ` Re: Type inheritance Rob Sargent <robjsargent@gmail.com>
2009-06-06 07:10 ` Re: Type inheritance Gianvito Pio <pio.gianvito@gmail.com>
2009-06-06 12:04 ` Re: Type inheritance Jasen Betts <jasen@xnet.co.nz>
2009-06-06 14:18 ` Re: Type inheritance Gianvito Pio <pio.gianvito@gmail.com>
2009-06-06 14:30 ` Re: Type inheritance Richard Broersma <richard.broersma@gmail.com>
2009-06-06 15:11 ` Re: Type inheritance Rob Sargent <robjsargent@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox