Postgresql – How to understand “object-relational”


I can't understand why the document say "PostgreSQL is an object-relational database management system"?Can somebody tell me the reason, or give me some article about this question.

Thanks a lot.

My experience:

  • I'm a beginner of database(and also beginner of postgresql. :D).
  • I implemented a service that provide several restful APIs for app. The logic of APIs all implement in plpgsql function, and app access them through nginx.

Best Answer

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.