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:
Table of Contents
|
1 2 3 4 5 6 7 8 |
test=# CREATE TABLE t_data (id serial, name text); CREATE TABLE test=# CREATE TABLE t_data_1 () INHERITS (t_data); CREATE TABLE test=# CREATE TABLE t_data_2 () INHERITS (t_data); CREATE TABLE |
All tables look the same because both tables inherit columns from the parent table:
|
1 2 3 4 5 6 7 |
test=# d t_data_2 Table 'public.t_data_2' Column | Type | Modifiers --------+---------+--------------- id | integer | name | text | Inherits: t_data |
Let us now add a column to one of the child tables:
|
1 2 |
test=# ALTER TABLE t_data_2 ADD COLUMN x int; ALTER TABLE |
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?
|
1 2 3 |
test=# ALTER TABLE t_data ADD COLUMN x int; NOTICE: merging definition of column 'x' for child 't_data_2' ALTER TABLE |
PostgreSQL will cleverly merge those two columns because they are identical.
Dropping columns is also an easy task. Let us see what happens if “x” is dropped again:
|
1 2 3 4 5 6 7 8 9 10 |
test=# ALTER TABLE t_data DROP COLUMN x; ALTER TABLE test=# d t_data Table 'public.t_data' Column | Type | Modifiers --------+---------+----------------------- id | integer | name | text | Number of child tables: 2 (Use d+ to list them.) |
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:
|
1 2 3 4 5 6 7 8 |
test=# d t_data_2 Table 'public.t_data_2' Column | Type | Modifiers --------+---------+----------------------- id | integer | name | text | x | integer | Inherits: t_data |
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 Twitter, Facebook, or LinkedIn.
+43 (0) 2622 93022-0
office@cybertec.at
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
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
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?