Re: [GENERAL] Inheritance and foreign keys

2017-05-25 Thread Jayadevan M
On Thu, May 25, 2017 at 6:00 PM, Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > The way I do it is the following : > - ensure a common sequence for the ID for all tables in the inheritance > tree (usually one parent and one or more children) > - enforce normal FK constraints for all F

Re: [GENERAL] Inheritance and foreign keys

2017-05-25 Thread Achilleas Mantzios
The way I do it is the following : - ensure a common sequence for the ID for all tables in the inheritance tree (usually one parent and one or more children) - enforce normal FK constraints for all FK relations within the same "realm"/"tenant"/"schema" etc, i.e. where it makes sense - for enforc

Re: [GENERAL] Inheritance and foreign keys

2017-05-25 Thread Andreas Kretschmer
Not possible - yet. Am 25. Mai 2017 13:48:59 MESZ schrieb Jayadevan M : >Hi, > >I designed three tables so that one table inherits another, and the >third >table references the parent table. If a record is inserted into the >third >table and the value does exist in the parent table indirectly, bec

Re: [GENERAL] Inheritance and trigger/FK propagation

2010-07-29 Thread Karsten Hilbert
On Thu, Jul 29, 2010 at 10:50:02AM +0200, Davor J. wrote: > For completeness, I think this link > (http://projects.nocternity.net/index.py/en/psql-inheritance) provides some > scripts you mention. Very interesting. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 453

Re: [GENERAL] Inheritance and trigger/FK propagation

2010-07-29 Thread Davor J.
"Karsten Hilbert" wrote in message news:20100728182051.gj2...@hermes.hilbert.loc... > On Tue, Jul 27, 2010 at 10:33:19AM +0200, Davor J. wrote: > >> Well... I found it out the hard way :). There are some extra caveats I >> have >> come along. There is the very clumsy ALTER TABLE table_name >> I

Re: [GENERAL] Inheritance and trigger/FK propagation

2010-07-28 Thread Karsten Hilbert
On Tue, Jul 27, 2010 at 10:33:19AM +0200, Davor J. wrote: > Well... I found it out the hard way :). There are some extra caveats I have > come along. There is the very clumsy ALTER TABLE table_name > INHERIT(parent_table) which simply presupposes the parent's columns, but > doesn't enforce it t

Re: [GENERAL] Inheritance and trigger/FK propagation

2010-07-28 Thread Karsten Hilbert
On Tue, Jul 27, 2010 at 10:36:16AM +0200, Davor J. wrote: > For me Vick's question just proves that inheritance in relational databases > is a complex issue. It shows that trigger propagation is not always desired, Now that's for sure :-) Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167

Re: [GENERAL] Inheritance and trigger/FK propagation

2010-07-27 Thread Davor J.
"Craig Ringer" wrote in message news:4c3ed37c.1070...@postnewspapers.com.au... > My understanding is that it's mostly an implementation limitation. In > other words, rather than any fundamental reason why it should not be > done, the issue is that nobody has gone and implemented it, tested it, >

Re: [GENERAL] Inheritance and trigger/FK propagation

2010-07-27 Thread Davor J.
""Karsten Hilbert"" wrote in message news:20100719182027.123...@gmx.net... >> On Thu, Jul 15, 2010 at 4:05 AM, Davor J. wrote: >> > It seems no secret that a child table will not fire a trigger defined >> > on >> > it's parent table. Various posts comment on this. But nowhere could I >> find a

Re: [GENERAL] Inheritance and trigger/FK propagation

2010-07-19 Thread Karsten Hilbert
> On Thu, Jul 15, 2010 at 4:05 AM, Davor J. wrote: > > It seems no secret that a child table will not fire a trigger defined on > > it's parent table. Various posts comment on this. But nowhere could I > find a > > reason for this. > > Do you want your trigger that redirects insert on parent tabl

Re: [GENERAL] Inheritance and trigger/FK propagation

2010-07-19 Thread Vick Khera
On Thu, Jul 15, 2010 at 4:05 AM, Davor J. wrote: > It seems no secret that a child table will not fire a trigger defined on > it's parent table. Various posts comment on this. But nowhere could I find a > reason for this. Do you want your trigger that redirects insert on parent table to the prope

Re: [GENERAL] Inheritance and trigger/FK propagation

2010-07-15 Thread Craig Ringer
On 15/07/10 16:05, Davor J. wrote: > It seems no secret that a child table will not fire a trigger defined on > it's parent table. Various posts comment on this. But nowhere could I find a > reason for this. [snip] > I read in the change logs of 8.4: "Force child tables to inherit CHECK > cons

Re: [GENERAL] Inheritance efficiency

2010-05-01 Thread Alban Hertroys
On 1 May 2010, at 12:56, Alban Hertroys wrote: > You could argue that some logic could be added to the handling of prepared > statements to insert query-subplans depending on what data you use for your > parameters, but then you're moving back in the direction of unprepared > statements (namely

Re: [GENERAL] Inheritance efficiency

2010-05-01 Thread Alban Hertroys
On 1 May 2010, at 5:33, John R Pierce wrote: > Greg Smith wrote: > my sql developer, who's been doing oracle for 15+ years, says postgres' > partitioning is flawed from his perspective because if you have a prepared > statement like.. > > SELECT fields FROM partitioned_table WHERE primarykey

Re: [GENERAL] Inheritance efficiency

2010-05-01 Thread Cédric Villemain
2010/5/1 John R Pierce : > Greg Smith wrote: >> >> Enterprise grade doesn't mean anything.  Partitioning designs that require >> thousands of child tables to work right are fundamentally misdesigned >> anyway, so there is no reason for any of the contributors to the project to >> work on improving

Re: [GENERAL] Inheritance efficiency

2010-05-01 Thread John R Pierce
Greg Smith wrote: Enterprise grade doesn't mean anything. Partitioning designs that require thousands of child tables to work right are fundamentally misdesigned anyway, so there is no reason for any of the contributors to the project to work on improving support for them. There are far too

Re: [GENERAL] Inheritance efficiency

2010-05-01 Thread Vincenzo Romano
2010/5/1 Greg Smith : > Vincenzo Romano wrote: >> >> While I can agree that "Enterprise grade" is a buzzword, it does mean >> something: "very large amount of data" among other. >> > > http://thedailywtf.com/Articles/Bitten_by_the_Enterprise_Bug.aspx > > It's quite straighforward to get PostgreSQL

Re: [GENERAL] Inheritance efficiency

2010-05-01 Thread Greg Smith
Vincenzo Romano wrote: While I can agree that "Enterprise grade" is a buzzword, it does mean something: "very large amount of data" among other. http://thedailywtf.com/Articles/Bitten_by_the_Enterprise_Bug.aspx It's quite straighforward to get PostgreSQL up and running with many terabytes

Re: [GENERAL] Inheritance efficiency

2010-04-30 Thread Vincenzo Romano
2010/5/1 Greg Smith : > Vincenzo Romano wrote: >> >> I argued that O(n) stuff will keep it away from "enterprise grade" >> applications. >> I've been told earlier that "It is fine for dozens of child tables, >> but not thousands; >> it does need improvement." >> This is not enterprise grade > > Ent

Re: [GENERAL] Inheritance efficiency

2010-04-30 Thread Greg Smith
Vincenzo Romano wrote: I argued that O(n) stuff will keep it away from "enterprise grade" applications. I've been told earlier that "It is fine for dozens of child tables, but not thousands; it does need improvement." This is not enterprise grade Enterprise grade doesn't mean anything. Partiti

Re: [GENERAL] Inheritance efficiency

2010-04-30 Thread Alex Hunsaker
On Fri, Apr 30, 2010 at 00:19, Vincenzo Romano wrote: > For example, the Linux kernel made the big jump with server hardware > thanks also to the O(1) schedulers. Uhh linux has not had a O(1) scheduler since 2.6.23, its supposedly O(log n) now. =) -- Sent via pgsql-general mailing list (pgsql

Re: [GENERAL] Inheritance efficiency

2010-04-30 Thread Bruce Momjian
Alvaro Herrera wrote: > Vincenzo Romano wrote: > > > This is not enterprise grade. > > "Enterprise grade" is nothing but a buzzword. Oh, it's also a moving > target. We've been not enterprise grade for years, always one feature > behind (and strangely, the one lacking feature is always the one

Re: [GENERAL] Inheritance efficiency

2010-04-30 Thread Alvaro Herrera
Vincenzo Romano wrote: > This is not enterprise grade. "Enterprise grade" is nothing but a buzzword. Oh, it's also a moving target. We've been not enterprise grade for years, always one feature behind (and strangely, the one lacking feature is always the one of interest to the complainant). --

Re: [GENERAL] Inheritance efficiency

2010-04-30 Thread Vincenzo Romano
2010/4/30 Alvaro Herrera : > Vincenzo Romano wrote: > >> In this specific case, if you think about "inheritance for >> partitioning" and you stick with the example idea of "one partition >> per month", then the current solution is more than OK. >> In the real world, that is not really the general c

Re: [GENERAL] Inheritance efficiency

2010-04-30 Thread Alvaro Herrera
Vincenzo Romano wrote: > In this specific case, if you think about "inheritance for > partitioning" and you stick with the example idea of "one partition > per month", then the current solution is more than OK. > In the real world, that is not really the general case, especially in > the "enterpri

Re: [GENERAL] Inheritance efficiency

2010-04-30 Thread David Fetter
On Fri, Apr 30, 2010 at 08:44:26AM +0200, Vincenzo Romano wrote: > >>> Should I move to an "enterprise grade" version of PostgreSQL? > >> > >> The enterprise grade version of PostgreSQL is the community > >> version. > >> > >> Proprietary forks exist, but they don't fix this kind of problem. > >> :

Re: [GENERAL] Inheritance efficiency

2010-04-29 Thread Vincenzo Romano
2010/4/30 Vincenzo Romano : > 2010/4/30 David Fetter : >> On Thu, Apr 29, 2010 at 11:29:36AM +0200, Vincenzo Romano wrote: >>> > No info about this point (partial indexes)? >>> > Is also this geared with linear algorithms ? >>> >>> Should I move to an "enterprise grade" version of PostgreSQL? >> >>

Re: [GENERAL] Inheritance efficiency

2010-04-29 Thread Vincenzo Romano
2010/4/30 David Fetter : > On Thu, Apr 29, 2010 at 11:29:36AM +0200, Vincenzo Romano wrote: >> > No info about this point (partial indexes)? >> > Is also this geared with linear algorithms ? >> >> Should I move to an "enterprise grade" version of PostgreSQL? > > The enterprise grade version of Post

Re: [GENERAL] Inheritance efficiency

2010-04-29 Thread David Fetter
On Thu, Apr 29, 2010 at 11:29:36AM +0200, Vincenzo Romano wrote: > > No info about this point (partial indexes)? > > Is also this geared with linear algorithms ? > > Should I move to an "enterprise grade" version of PostgreSQL? The enterprise grade version of PostgreSQL is the community version.

Re: [GENERAL] Inheritance efficiency

2010-04-29 Thread Vincenzo Romano
2010/4/26 Vincenzo Romano : > 2010/4/26 Bruce Momjian : >> Vincenzo Romano wrote: >>> Hi all. >>> >>> I'm wondering how efficient the inheritance can be. >>> I'm using the constraint exclusion feature and for each child table >>> (maybe but one) I have a proper CHECK constraint. >>> How efficient c

Re: [GENERAL] Inheritance efficiency

2010-04-26 Thread Vincenzo Romano
2010/4/26 Bruce Momjian : > Vincenzo Romano wrote: >> Hi all. >> >> I'm wondering how efficient the inheritance can be. >> I'm using the constraint exclusion feature and for each child table >> (maybe but one) I have a proper CHECK constraint. >> How efficient can the query planner be in choosing t

Re: [GENERAL] Inheritance efficiency

2010-04-26 Thread Bruce Momjian
Vincenzo Romano wrote: > Hi all. > > I'm wondering how efficient the inheritance can be. > I'm using the constraint exclusion feature and for each child table > (maybe but one) I have a proper CHECK constraint. > How efficient can the query planner be in choosing the right child > tables in the ca

Re: [GENERAL] Inheritance on foreign key

2009-10-16 Thread decibel
On Oct 12, 2009, at 1:21 PM, Erik Jones wrote: On Oct 9, 2009, at 9:57 AM, Takeichi Kanzaki Cabrera wrote: Hi everybody, I need your help. I have a hierarchy of tables, and other table that has a foreign key with the top table of the hierarchy, can I insert a value into the "other" table where t

Re: [GENERAL] Inheritance on foreign key

2009-10-16 Thread decibel
On Oct 12, 2009, at 1:21 PM, Erik Jones wrote: On Oct 9, 2009, at 9:57 AM, Takeichi Kanzaki Cabrera wrote: Hi everybody, I need your help. I have a hierarchy of tables, and other table that has a foreign key with the top table of the hierarchy, can I insert a value into the "other" table where t

Re: [GENERAL] Inheritance on foreign key

2009-10-12 Thread Erik Jones
On Oct 9, 2009, at 9:57 AM, Takeichi Kanzaki Cabrera wrote: Hi everybody, I need your help. I have a hierarchy of tables, and other table that has a foreign key with the top table of the hierarchy, can I insert a value into the "other" table where the value it reference is not on the parent tab

Re: [GENERAL] Inheritance question

2009-01-16 Thread Erik Jones
On Jan 16, 2009, at 9:49 AM, Glyn Astill wrote: Hi chaps, I've got a question about inheritance here, and I think I may have gotten the wrong end of the stick as to how it works, or at least when to use it. What I intended to do was have a schema "audit" with an empty set of tables in

Re: [GENERAL] inheritance. more.

2008-05-01 Thread Gregory Stark
"Nathan Boley" <[EMAIL PROTECTED]> writes: > Because people can be smarter about the data partitioning. > > Consider a table of users. Some are active, most are not. The active > users account for nearly all of the users table access, but I still > (occasionally) want to access info about the inac

Re: [GENERAL] inheritance. more.

2008-05-01 Thread Jeremy Harris
Nathan Boley wrote: Because people can be smarter about the data partitioning. Consider a table of users. Some are active, most are not. The active users account for nearly all of the users table access, but I still (occasionally) want to access info about the inactive users. Partitioning users

Re: [GENERAL] inheritance. more.

2008-05-01 Thread Nathan Boley
Because people can be smarter about the data partitioning. Consider a table of users. Some are active, most are not. The active users account for nearly all of the users table access, but I still (occasionally) want to access info about the inactive users. Partitioning users into active_users and

Re: [GENERAL] inheritance. more.

2008-05-01 Thread Jeremy Harris
Gurjeet Singh wrote: One of the advantages of breaking up your data into partitions, as professed by Simon (I think) (and I agree), is that you have smaller indexes, which improve performance. And maybe having one huge index managing the uniqueness across partitioned data just defeats the idea o

Re: [GENERAL] inheritance. more.

2008-04-30 Thread Tom Lane
"Karsten Hilbert" <[EMAIL PROTECTED]> writes: >> And maybe having one huge index managing the uniqueness across partitioned >> data just defeats the idea of data partitioning! > Except when you want uniqueness across all partitions. Well, the point was that if the partitioning arrangement guarant

Re: [GENERAL] inheritance. more.

2008-04-30 Thread Karsten Hilbert
> And maybe having one huge index managing the uniqueness across partitioned > data just defeats the idea of data partitioning! Except when you want uniqueness across all partitions. Karsten -- Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! Ideal für Modem und ISDN: http://w

Re: [GENERAL] inheritance. more.

2008-04-30 Thread Gurjeet Singh
On Tue, Apr 29, 2008 at 11:47 AM, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: > On Tue, Apr 29, 2008 at 06:31:30AM +0530, Gurjeet Singh wrote: > > On Mon, Apr 28, 2008 at 8:01 PM, Erik Jones <[EMAIL PROTECTED]> wrote: > > > Postgres doesn't yet handle inheritance of constraints from parent t

Re: [GENERAL] inheritance. more.

2008-04-28 Thread Martijn van Oosterhout
On Tue, Apr 29, 2008 at 06:31:30AM +0530, Gurjeet Singh wrote: > On Mon, Apr 28, 2008 at 8:01 PM, Erik Jones <[EMAIL PROTECTED]> wrote: > > Postgres doesn't yet handle inheritance of constraints from parent to > > child tables via inheritance. > Was it done by design or was it a limitation we coul

Re: [GENERAL] inheritance. more.

2008-04-28 Thread Erik Jones
On Apr 28, 2008, at 8:01 PM, Gurjeet Singh wrote: On Mon, Apr 28, 2008 at 8:01 PM, Erik Jones <[EMAIL PROTECTED]> wrote: Postgres doesn't yet handle inheritance of constraints from parent to child tables via inheritance. Was it done by design or was it a limitation we couldn't get over?

Re: [GENERAL] inheritance. more.

2008-04-28 Thread Gurjeet Singh
On Mon, Apr 28, 2008 at 8:01 PM, Erik Jones <[EMAIL PROTECTED]> wrote: > > Postgres doesn't yet handle inheritance of constraints from parent to > child tables via inheritance. > > Was it done by design or was it a limitation we couldn't get over? Best regards, -- [EMAIL PROTECTED] [EMAIL PROTEC

Re: [GENERAL] inheritance. more.

2008-04-28 Thread Erik Jones
On Apr 27, 2008, at 8:23 PM, Tom Allison wrote: create table master ( id serial, mdn varchar(11), meid varchar(18), min varchar(11), constraint mmm_master unique (mdn, meid, min) ); insert into master(mdn, meid, min) select mdn, meid, min from test_data where meid != '00' limi

Re: [GENERAL] inheritance...

2008-04-27 Thread Klint Gore
Tom Allison wrote: Am I missing something in the fine print? fine print = see 5.8.1 Caveats on http://www.postgresql.org/docs/8.3/interactive/ddl-inherit.html klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773

Re: [GENERAL] Inheritance problem when restoring db

2007-10-29 Thread Sebastjan Trepca
Great, super thanks! Sebastjan On 10/29/07, Tom Lane <[EMAIL PROTECTED]> wrote: > I wrote: > > "Sebastjan Trepca" <[EMAIL PROTECTED]> writes: > >> This is how to reproduce this issue: > >> ... > >> inh_test=# alter table capitals inherit cities; > > > Fascinating. pg_dump is almost smart enough

Re: [GENERAL] Inheritance problem when restoring db

2007-10-29 Thread Tom Lane
I wrote: > "Sebastjan Trepca" <[EMAIL PROTECTED]> writes: >> This is how to reproduce this issue: >> ... >> inh_test=# alter table capitals inherit cities; > Fascinating. pg_dump is almost smart enough to get this right, ... I've fixed this --- if you need a patch right away, see here: http://ar

Re: [GENERAL] Inheritance problem when restoring db

2007-10-28 Thread Tom Lane
"Sebastjan Trepca" <[EMAIL PROTECTED]> writes: > This is how to reproduce this issue: > ... > inh_test=# alter table capitals inherit cities; Fascinating. pg_dump is almost smart enough to get this right, except that what it spits out is ALTER TABLE capitals ALTER COLUMN id SET DEFAULT nextval(

Re: [GENERAL] Inheritance problem when restoring db

2007-10-28 Thread Sebastjan Trepca
Hi, sorry for late response and lack of details. Postgresql version is 8.2.5 . This is how to reproduce this issue: inh_test=# CREATE TABLE cities ( inh_test(# id serial, inh_test(# nametext, inh_test(# population float, inh_test(# altitudeint -- in f

Re: [GENERAL] Inheritance foreign key unexpected behaviour

2007-10-22 Thread Scott Ribe
PostgreSQL foreign keys won't enforce restrictions the way you want them to; you'll have to use a trigger. And at that point, you might as well consider alternative designs... -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of br

Re: [GENERAL] Inheritance foreign key unexpected behaviour

2007-10-21 Thread Scott Marlowe
On 10/20/07, M. van Egmond <[EMAIL PROTECTED]> wrote: > Hi all, > > Im trying to use table inheritance in my database. I need it because i want > to be able to link any object in the database to another. So i created a > table my_object which has a serial, nothing more. All the other tables in > th

Re: [GENERAL] Inheritance fixing timeline? (Was "Inherited FK Indexing")

2007-10-01 Thread Alvaro Herrera
Webb Sprague escribió: > > > Is it > > > possible to have FK that spans into child tables? > > > > This is a well known (and documented, see [1]) deficiency. It's due to > > the current implementation of indices, which are bound to exactly one > > table, meaning they do return a position within

Re: [GENERAL] Inheritance problem when restoring db

2007-10-01 Thread Tom Lane
"Sebastjan Trepca" <[EMAIL PROTECTED]> writes: > Current state: > Table B has a primary key with sequence b_seq. Table A also has a > primary key with sequence a_seq. In view of the fact that primary keys aren't inherited, and do not "have sequences", this description is uselessly imprecise. Ple

Re: [GENERAL] Inheritance fixing timeline? (Was "Inherited FK Indexing")

2007-10-01 Thread Scott Ribe
> Is this set to be fixed in any particular release? Depending on what you're doing, this may be overkill, but: I have child tables that not only need FK constraints, but also triggers and the functions called by the triggers. So instead of writing this over and over again, I eventually wrote a si

Re: [GENERAL] Inheritance and shared sequence

2007-06-07 Thread Oliver Elphick
On Thu, 2007-06-07 at 09:44 +0100, Richard Huxton wrote: > Sebastjan Trepca wrote: > > Hi, > > > > as I understood, when you create a table which inherits some other > > table, the constraints and indexes do not go to the child table hence > > you have to create a separate ones in there. That mean

Re: [GENERAL] Inheritance and shared sequence

2007-06-07 Thread Richard Huxton
Sebastjan Trepca wrote: Hi, as I understood, when you create a table which inherits some other table, the constraints and indexes do not go to the child table hence you have to create a separate ones in there. That means you cannot depend that you won't have duplicate IDs in both tables. Right?

Re: [GENERAL] Inheritance question

2007-05-30 Thread Andrew Sullivan
On Thu, May 24, 2007 at 04:44:53PM -0700, Raymond C. Rodgers wrote: > The question, and point, is this: Is there an alternate way of > accomplishing read and write functionality similar to what inheritance > offers but allowing me to map the columns as I desire? The read only It isn't clear fro

Re: [GENERAL] inheritance

2006-12-20 Thread Bruno Wolff III
On Tue, Dec 19, 2006 at 11:20:35 +0100, Udo Zubel <[EMAIL PROTECTED]> wrote: > Hi Im engineering a PG database with my workmate. > Unfortunately the inheritance feature seems not to be able to solve my > problem. > I have a table "order" with an orderID, each order has 1 to n types of > article

Re: [GENERAL] inheritance and index use (similar to UNION ALL)

2006-12-11 Thread Karsten Hilbert
Further testing has revealed that, indeed, PG 8.2 speeds up our use of child tables ! The query in question went down from 10 minutes to *under a second* just by running against 8.2 :-) Now, that's some gain ! Thanks to the PostgreSQL developers. Karsten, GNUmed team On Sun, Dec 10, 2006 at

Re: [GENERAL] Inheritance and unique constraints

2006-07-05 Thread John Purser
On Wed, 05 Jul 2006 15:51:23 +0200 "Christian Rengstl" <[EMAIL PROTECTED]> wrote: > Hi everyone, > > i hope (and i'm sure) somebody can answer my question: if i have a > master table and several "child" tables, do the child tables inherit > the unique constraint(s) defined for the master table or

Re: [GENERAL] inheritance and table

2006-06-27 Thread nik600
ok i have understand many thanks to all! On 6/27/06, Erik Jones <[EMAIL PROTECTED]> wrote: nik600 wrote: > hi > > i don't have understand how works inheritance of tables... > > if table B inherits from table A > > - A and B must share primary keys? > > - if i isert record in B the record is rep

Re: [GENERAL] inheritance and table

2006-06-26 Thread arie nugraha
Inheritance in postgre means you will have same fields definition like the inherited table plus its own fields. So if table B is inherit table A, table B will have same field definition like A plus table B own unique field(s). It wont share primary keys, table B just have primary key in th

Re: [GENERAL] inheritance and table

2006-06-26 Thread Erik Jones
nik600 wrote: hi i don't have understand how works inheritance of tables... if table B inherits from table A - A and B must share primary keys? - if i isert record in B the record is replaced in A ? can i avoid this? i would like to have this scenario: table A table B inheridts from A tab

Re: [GENERAL] inheritance and table

2006-06-26 Thread Richard Broersma Jr
> i don't have understand how works inheritance of tables... > if table B inherits from table A > - A and B must share primary keys? No, currently there is no unique constraint that will force uniqueness across parent/child/sibling tables. Just think of them as being nothing more than seperate

Re: [GENERAL] Inheritance

2006-03-28 Thread Paolo Sala
Richard Broersma Jr scrisse in data 03/28/06 15:18: No it isn't. But I remember reading on one of the lists that it was on the to-do list for 8.2. However, I do not know how high it is on the list of things to do. So I imagine that there is the potential that it might not be added. I'll wai

Re: [GENERAL] Inheritance

2006-03-28 Thread Richard Broersma Jr
> Jebus scrisse in data 03/27/06 19:03: > > >I could be wrong but primary keys and other constraints are not inherited. do you know if > this problem is solved in postgres 8.1? No it isn't. But I remember reading on one of the lists that it was on the to-do list for 8.2. However, I do not kno

Re: [GENERAL] Inheritance

2006-03-27 Thread Paolo Sala
Jebus scrisse in data 03/27/06 19:03: I could be wrong but primary keys and other constraints are not inherited. Thank you very much Jebus; in effect I've found in the mailing list archives a 2003 thread "INHERITS and Foreign keys" that claim the same problem. Someone (Stephan Szabo) answer

Re: [GENERAL] Inheritance Algebra

2005-12-23 Thread Mike Rylander
On 12/23/05, Karsten Hilbert <[EMAIL PROTECTED]> wrote: > On Thu, Dec 22, 2005 at 05:05:49PM -0700, Trent Shipley wrote: > > > On Wednesday 2005-12-21 07:50, Karsten Hilbert wrote: > > > > I would assume quite a few people would use table > > > inheritance in a simple way were it available in a mor

Re: [GENERAL] Inheritance Algebra

2005-12-23 Thread Karsten Hilbert
On Thu, Dec 22, 2005 at 05:05:49PM -0700, Trent Shipley wrote: > On Wednesday 2005-12-21 07:50, Karsten Hilbert wrote: > > I would assume quite a few people would use table > > inheritance in a simple way were it available in a more > > convenient fashion: to transport fields, primary and foreign

Re: [GENERAL] Inheritance Algebra

2005-12-22 Thread Trent Shipley
On Wednesday 2005-12-21 07:50, Karsten Hilbert wrote: > On Wed, Dec 21, 2005 at 01:52:34PM +0100, Martijn van Oosterhout wrote: > > On Sun, Dec 04, 2005 at 10:59:10PM -0700, Trent Shipley wrote: > > > Relational Constraint Inheritance Algebra > > > With regard to class and attribute uniqueness > >

Re: [GENERAL] Inheritance Algebra

2005-12-21 Thread Karsten Hilbert
On Wed, Dec 21, 2005 at 01:52:34PM +0100, Martijn van Oosterhout wrote: > On Sun, Dec 04, 2005 at 10:59:10PM -0700, Trent Shipley wrote: > > Relational Constraint Inheritance Algebra > > With regard to class and attribute uniqueness > > It's taken a while to digest this and sorry for the delay. Wh

Re: [GENERAL] Inheritance Algebra

2005-12-21 Thread Martijn van Oosterhout
On Sun, Dec 04, 2005 at 10:59:10PM -0700, Trent Shipley wrote: > Relational Constraint Inheritance Algebra > With regard to class and attribute uniqueness It's taken a while to digest this and sorry for the delay. While I find the ideas intreguing there is a little voice in the back of my head a

Re: [GENERAL] Inheritance Algebra

2005-12-06 Thread Karsten Hilbert
Trent, although I cannot contribute much of anything to your line of thought I'd encourage you to keep on with it as it'd be highly desirable (for GNUmed at least) to have a stronger/ more encompassing inheritance solution in PostgreSQL. Karsten, GNUmed developer -- GPG key ID E4071346 @ wwwkeys

Re: [GENERAL] Inheritance vs. LIKE - need advice

2005-08-15 Thread Jeff Davis
William Bug wrote: > > > I'm not certain I understand what you mean here? Are you recommending > all application layer interaction with tables using INHERIT should be > done via a VIEW intermediary? If so, wouldn't the VIEW (built from a > "SELECT ... ONLY...") then be as dependent on the f

Re: [GENERAL] Inheritance vs. LIKE - need advice

2005-08-10 Thread William Bug
Once again, many many thanks Jeff for taking the time to think through these issues and provide your well-informed comments & opinions! On Aug 10, 2005, at 4:09 PM, Jeff Davis wrote: William Bug wrote: As you say, both LIKE & INHERIT are a bit anemic and unnecessary, since what they provi

Re: [GENERAL] Inheritance vs. LIKE - need advice

2005-08-10 Thread Jeff Davis
William Bug wrote: > As you say, both LIKE & INHERIT are a bit anemic and unnecessary, since > what they provide can be implemented via VIEWs and RULEs/ TRIGGERs. I'd I'd like to point out that INHERITS is unique, although I'm not sure all of the exact differences. The main difference that I see

Re: [GENERAL] Inheritance vs. LIKE - need advice

2005-08-06 Thread Jeff Davis
William Bug wrote: > Hi All, > > Sorry to bring up the topic of PostgreSQL inheritance again, but after > going through the archives and Google results, I still don't have a > clear sense of whether my plan to implement a schema I'm working on is > the preferred way to go. > > First, I'd like

Re: [GENERAL] Inheritance and such

2005-04-03 Thread Daniel Schuchardt
Stephane Bortzmeyer schrieb: I have a problem which MAY be in the same category. CREATE TABLE base ( id serial not null primary key, ); CREATE TABLE specialized ( ) INHERITS base; Now, I try to set up a trigger AFTER UPDATE ON base but, when I update "specialized", the trigger is not called. Same

Re: [GENERAL] Inheritance and such

2005-04-03 Thread Stephane Bortzmeyer
On Fri, Apr 01, 2005 at 09:51:25AM -0600, John Hughes <[EMAIL PROTECTED]> wrote a message of 49 lines which said: > I ran into a brick wall when I realized that inheritance in postgres > isnt really there... I have a problem which MAY be in the same category. CREATE TABLE base ( id serial n

Re: [GENERAL] Inheritance in Postgres ?

2004-11-30 Thread Richard Huxton
Tatu Salminen wrote: Hi, Is there going to be full support for inheritance (tables) in future ? Are there going to be any improvements about inheritance ? It all depends on whether anyone with the relevant interest and skills steps forward to develop improvements. At present it seems low on th

Re: [GENERAL] Inheritance and column references problem

2004-02-24 Thread Stephan Szabo
> Is this a bug, or a feature? It seems I can't make a column reference > work directly with the table that gets inherited by the others. Neither > can I make a column reference work with a table that *inherits* the > toinherit table. If I can't get this to work, I'll have to revert back > to not u

Re: [GENERAL] inheritance vs performance

2004-02-13 Thread Steve Atkins
On Fri, Feb 13, 2004 at 01:51:24PM +, Richard Huxton wrote: > On Friday 13 February 2004 10:59, Karsten Hilbert wrote: > > > Well, thousands of tables is probably "too much", but a hundred tables or > > > two in a database shouldn't cause problems. Don't see why you'd want them > > > though. >

Re: [GENERAL] Inheritance and foreign keys

2003-12-09 Thread Jan Wieck
Stephan Szabo wrote: On Tue, 9 Dec 2003, Brendan Jurd wrote: Hi all, I read on the manual page for Inheritance that: "A limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children.

Re: [GENERAL] Inheritance and foreign keys

2003-12-08 Thread Brendan Jurd
Peter Eisentraut wrote: Brendan Jurd wrote: I have a few projects that could benefit from inherited table structure, and it's a very cool idea, but this inability of indexes to include derived tables is a real functionality-killer. It's not "Object Relational" if the objects can

Re: [GENERAL] Inheritance and foreign keys

2003-12-08 Thread Stephan Szabo
On Tue, 9 Dec 2003, Brendan Jurd wrote: > Hi all, > > I read on the manual page for Inheritance that: > > "A limitation of the inheritance feature is that indexes (including > unique constraints) and foreign key constraints only apply to single > tables, not to their inheritance children. Thus, in

Re: [GENERAL] Inheritance and foreign keys

2003-12-08 Thread Stephan Szabo
On Mon, 8 Dec 2003, Stephan Szabo wrote: > On Tue, 9 Dec 2003, Brendan Jurd wrote: > > > Hi all, > > > > I read on the manual page for Inheritance that: > > > > "A limitation of the inheritance feature is that indexes (including > > unique constraints) and foreign key constraints only apply to sin

Re: [GENERAL] Inheritance & multiple-value fields

2003-08-04 Thread scott.marlowe
On Sat, 2 Aug 2003, Vernon Smith wrote: > > We usually use another table for a multi-valued field. Is possible > having a single multi-valued field table for all tables in the same > heredity, other than having a multi-valued table for every single > tables in the heredity? The SQL 3 standard

Re: [GENERAL] Inheritance and Constraints

2003-07-19 Thread Stephan Szabo
On Wed, 16 Jul 2003, Andrew Vit wrote: > CREATE TABLE products ( > id serial NOT NULL PRIMARY KEY, > name varchar(64) NOT NULL, > ); > > CREATE TABLE thing_1 ( > foo smallint, > bar text, > ) inherits (products); > > CREATE TABLE grading ( > thingid int NOT NULL REFERENCES thi

Re: [GENERAL] Inheritance & Indexes

2003-06-25 Thread Stephan Szabo
On Wed, 25 Jun 2003, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > I *think* 7.4 may be smarter about > > implying these conditions as well. > > Not really. AFAIR the Append-style plan is the only thing you can get > out of the planner for inheritance trees. This works well en

Re: [GENERAL] Inheritance & Indexes

2003-06-24 Thread Stephan Szabo
On Tue, 24 Jun 2003, Alan Williams wrote: > > On Tue, 24 Jun 2003, Stephan Szabo wrote: > > > hs.exon.2=> explain select * from ga_psr_transcript_1 t, > > ga_psr_exon_1e where e.parent = t.id; > > > QUERY PLAN > > > > >

Re: [GENERAL] Inheritance & Indexes

2003-06-24 Thread Alan Williams
On Tue, 24 Jun 2003, Stephan Szabo wrote: > > hs.exon.2=> explain select * from ga_psr_transcript_1 t, > ga_psr_exon_1e where e.parent = t.id; > > QUERY PLAN > > > > -

Re: [GENERAL] Inheritance & Indexes

2003-06-24 Thread Stephan Szabo
On Tue, 24 Jun 2003, Alan Williams wrote: > hs.exon.2=> \d ga_psr_transcript_1 > Table "public.ga_psr_transcript_1" > Column| Type | Modifiers > --++--- > id | integer| not null > parent

Re: [GENERAL] Inheritance c.cities ??

2001-09-13 Thread R Talbot
R Talbot wrote: > R Talbot wrote: > > > Looking at the Postgresql manual in the inheritance section I see > > abbreviations. > > > > i.e. > > SELECT c.name, c.altitude > > FROM cities* c > > WHERE c.altitude > 500; > > > > c.name of course refering to cities.name > > My question is where is the

Re: [GENERAL] Inheritance c.cities ??

2001-09-13 Thread Thurstan R. McDougle
R Talbot wrote: > > Looking at the Postgresql manual in the inheritance section I see > abbreviations. > > i.e. > SELECT c.name, c.altitude > FROM cities* c > WHERE c.altitude > 500; > > c.name of course refering to cities.name > My question is where is there an alias reference of c AS citie

Re: [GENERAL] Inheritance & Referential Integrity

2001-05-14 Thread Stephan Szabo
On Mon, 14 May 2001, Andrew Snow wrote: > I note that it still is not possible (v7.1.1) to have a table with a foreign > key check on an inherited column for rows in child tables. Will this ever > be supported, and what is the status of work on this feature? It will eventually be supported. Th

RE: [GENERAL] Inheritance and Referencing troubles... (confusing problem)

2001-05-09 Thread Christian Marschalek
Thanks for your help... Inheritance seem to be useless to me now ;) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html

Re: [GENERAL] Inheritance and referential integritry in 7.0.3

2001-04-08 Thread Eric G. Miller
On Sun, Apr 08, 2001 at 01:04:16PM +0800, Alastair D'Silva wrote: > I am using PostgreSQL 7.0.3 and have the following schema: > > CREATE TABLE "products" ( > "id" SERIAL NOT NULL PRIMARY KEY, > "name" text NOT NULL, > "description" text NOT NULL, > "brand" text NO

  1   2   >