To someone who has experience using inheritance in PostgreSQL: Is it worth using it, or better not to? In which situation you would use it?
To be honest, I do not fully understand the difference between the relational and OO models...
-
9@Dr.PersonPersonII Gentlemen! I do not mind when you edit my posts for clarity, but not when you change the meaning. To my mind: "To be true I'm a little bit in doubt about mixing relational and OO models..." and "I do not fully understand the difference between the relational and OO models" are too different things! Would be nice if you would restore my words! Thanks.Anton Prokofiev– Anton Prokofiev2011-06-22 22:21:24 +00:00Commented Jun 22, 2011 at 22:21
-
Prokofviev. I didn't intend to change your meaning and I do not see the split-hair difference that you are on about. In light of the extreme lack of grammar in your original post (I thought I was helping a non-native speaker), I'm in disbelief that you think I've somehow ruined your garbled question and the angry tone of your reply. You simply could have fixed the "problem" in 5 seconds and all would have been well. You can change the edits yourself! I'm not going to bother.SO Stinks– SO Stinks2011-06-23 09:27:55 +00:00Commented Jun 23, 2011 at 9:27
-
2Nothing personal! I'm not a big fun of this StackExhange feature. It remained me Orwell's 1984 too much...Anton Prokofiev– Anton Prokofiev2011-06-23 18:06:19 +00:00Commented Jun 23, 2011 at 18:06
Add a comment
|
2 Answers
Probably not, there are caveats to PostgreSQL table inheritance, such as no globally unique constraints, so you lose many of the consistency guarantees. Also writing well-performing queries can be quite a challenge. As pointed out by Scott, PostgreSQL inheritance is only really useful for table partitioning where it's a performance tradeoff to begin with.
There are 2 common ways to use standard SQL idioms for class inheritance:
- Every object has an individual row in a superclass table, and subclass objects also have a row in a subclass-specific table, that refers to superclass fields by a foreign-key reference.
- Just lump all superclass and subclass fields in a single big table, and leave them be NULLs where their value is not applicable. This works particularly well in PostgreSQL, because NULL values only consume 1 bit per row, and adding/removing fields to existing tables is very fast (regardless of the amount of data). You could possibly write a trigger to verify that required fields for a specific type of class are present.
1 Comment
Igor
Sorry could not get your second point "Just lump all superclass and subclass ..." Was just hanging around with the inheritance and postgres. Finally how to get the foreign key constraints to work with inheritance? Triggers? Checks? Some manual/links? Thanks.
Its nice but be sure your understand the caveats outlined in the manual before using it. Currently the way it handles constraints is a bit rough but its on the todo list. It is particularly useful with partitioning. A more OO example would be inheriting from the people table to create an employees table.
Of course the downside is that its not portable to any other rdbms's so if you had to rehost a database on another rdbms you'd have to rewrite a bunch of stuff.
