CYBERTEC PostgreSQL Logo

Inheritance - One more reason to love PostgreSQL

07.2016 / Category: / Tags: |

After doing full-time PostgreSQL consulting for over 16 years now, I actually don't remember a time without inheritance anymore. Of course things have improved over time, but in my head it has always been there, and it has always just worked as expected. After so many years, I still love this feature, because it offers some nice details which are simply adorable. Here is one of those details I really like:

Some nice details concerning inheritance

Let us assume that we create a parent table and two child tables:

All tables look the same because both tables inherit columns from the parent table:

Adding columns

Let us now add a column to one of the child tables:

In PostgreSQL it is absolutely possible that a child table has more columns than the parent table. This is nothing special. However, what happens if we try to add the same column to the parent table as well?

PostgreSQL will cleverly merge those two columns because they are identical.

Dropping columns

Dropping columns is also an easy task. Let us see what happens if “x” is dropped again:

Of course dropping the table ensures that the column is also dropped in the child tables.

However, this does not hold true for t_data_2:

Isn't that wonderful? PostgreSQL “remembers” that this is a merged column and ensures that the column stays as it is. PostgreSQL only cascades the column removal to t_data_1, which also got the column through the parent table.


In case you need any assistance, please feel free to contact us.
 

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.

2 responses to “Inheritance - One more reason to love PostgreSQL”

  1. Hello. Thank you for the article.

    I wonder how do you solve an issue when there is a table, for instance

    CREATE TABLE t_data_ref (
    t_data_ref_id SERIAL PRIMARY KEY
    t_data_id INTEGER REFERENCES t_data (id));

    and you need it to reference records independently whether the record is in t_data, t_data_1, t_data_2?

    Considering the documentation https://www.postgresql.org/docs/current/static/ddl-inherit.html, there is no a good way to solve it: "Specifying that another table's column REFERENCES cities(name) would allow the other table to contain city names, but not capital names. There is no good workaround for this case."

    What is your solution for this case?

  2. Hello,
    Thank you for your article.

    I want to know the advantages or disadvantages of inheritance in postgreSQL

    To deepen my research, I built tables with a lot of records

    CREATE TABLE t_data (....); ---
    CREATE TABLE t_data_1 () INHERITS (t_data); - 2 265 287 rows
    CREATE TABLE t_data_2 () INHERITS (t_data); - 973 436 rows
    CREATE TABLE t_data_3 () INHERITS (t_data); - 1,065,846 rows
    CREATE TABLE t_data_4 () INHERITS (t_data); - 927 810 rows
    CREATE TABLE t_data_5 () INHERITS (t_data); - 1,486,461 rows
    CREATE TABLE t_data_6 () INHERITS (t_data); - 1 142 280 rows
    CREATE TABLE t_data_7 () INHERITS (t_data); - 1,121,704 rows
    CREATE TABLE t_data_8 () INHERITS (t_data); - 823 926 rows

    I created a single t_data_all table containing (without inheritance) all the data of my 8 tables
    (9 806 750 rows)

    I compared the execution time between queries based on t_data and t_data_all
    Simple query:
    SELECT ... FROM t_data WHERE ... ORDER BY ...
    SELECT ... FROM t_data_all WHERE ... ORDER BY ...

    same time

    On the other hand, important advantage (time/2) to inheritance with more complex queries
    - SQL with join on another table
    - GROUP BY

    Have you made the same observation?

    Thank you in advance for your answers

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram