snippetsqlMinor
How to understand "object-relational"?
Viewed 0 times
relationalunderstandobjecthow
Problem
I can't understand why the documentation says
PostgreSQL is an object-relational database management system
Can somebody tell me the reason, or link to an article talking about this?
Thanks a lot.
My experience:
PostgreSQL is an object-relational database management system
Can somebody tell me the reason, or link to an article talking about this?
Thanks a lot.
My experience:
- I'm a beginner of databases (and also beginner of postgresql :D）.
- I implemented a service that provide several restful APIs for app. The logic of APIs is implemented in PL/pgSQL functions, and the app accesses them through nginx.
Solution
I question whether it should be described as "object-relational", really.
That statement refers to PostgreSQL's inheritance features. They are useful, but limited. You can't have foreign keys pointing to a parent table and all children, nor can you have a unique constraint that constrains a parent table and all children. Triggers must be defined on each child, not just the parent. Etc.
Another issue is that Pg's inheritance features don't play well with ORMs. Most ORMs want to map inheritance as decomposition by reference, so that if "A" extends "B", then you'll have a table "A" with all A's fields, and a table "B" with the extra fields and a foreign key back to "A".
PostgreSQL's inheritance doesn't have that foreign key back to A. Instead, each row of "B" has all fields of both "A" and "B". When you query "A", you see the common columns from "B" as well. This confuses ORMs, becaue it means there's no relationship they can see between "A" and "B", so they don't tend to cope with mapping their understanding of inheritance onto PostgreSQL's understanding of inheritance.
That tends to limit use of PostgreSQL inheritance to areas where you're not using an ORM, but areas where inheritance is useful is just where you're most likely to use an ORM.
The inheritance features are quite handy for certain kinds of designs, you just have to accept the limitations. They're a lot faster to use than the typical ORM-style approach of separate tables with foreign keys, for one thing.
Your situation, where you're coding in PL/PgSQL, is one where the inheritance features might prove very useful to you.
That statement refers to PostgreSQL's inheritance features. They are useful, but limited. You can't have foreign keys pointing to a parent table and all children, nor can you have a unique constraint that constrains a parent table and all children. Triggers must be defined on each child, not just the parent. Etc.
Another issue is that Pg's inheritance features don't play well with ORMs. Most ORMs want to map inheritance as decomposition by reference, so that if "A" extends "B", then you'll have a table "A" with all A's fields, and a table "B" with the extra fields and a foreign key back to "A".
PostgreSQL's inheritance doesn't have that foreign key back to A. Instead, each row of "B" has all fields of both "A" and "B". When you query "A", you see the common columns from "B" as well. This confuses ORMs, becaue it means there's no relationship they can see between "A" and "B", so they don't tend to cope with mapping their understanding of inheritance onto PostgreSQL's understanding of inheritance.
That tends to limit use of PostgreSQL inheritance to areas where you're not using an ORM, but areas where inheritance is useful is just where you're most likely to use an ORM.
The inheritance features are quite handy for certain kinds of designs, you just have to accept the limitations. They're a lot faster to use than the typical ORM-style approach of separate tables with foreign keys, for one thing.
Your situation, where you're coding in PL/PgSQL, is one where the inheritance features might prove very useful to you.
Context
StackExchange Database Administrators Q#77340, answer score: 4
Revisions (0)
No revisions yet.