MySQL users can easily be confused by the seemingly infinite differences between MySQL and PostgreSQL. The good news is that for someone familiar with MySQL, they have the basics of databases covered and can explore some of the treasures to be discovered in the new PostgreSQL environment.
One of the interesting facets that you need to explore that has no corresponding counterpart in the MySQLverse is table inheritance. If you are a big fan of data normalization, this feature will catch your attention. You can start with a really minimalistic parent table and build upon it.
Example
In the following example, pretend that an imaginary company has products, inventory for that product, and warehouses where that product inventory is held. Tables for products, inventory, and warehouses are created. The inherits keyword is new for someone from the MySQL universe. In the example below, inventory inherits product information from the product table. And the warehouse table inherits product and inventory information from those tables, respectively.
1 2 3 4 5 6 7 8 9 10 11 | test=# create table product (id int not null, name text, code int, cost real, primary key (id)); CREATE TABLE test=# create table inventory (quantity int not null) inherits (product); CREATE TABLE test=# create table warehouse (location int not null) inherits (product, inventory); NOTICE: merging multiple inherited definitions of column "id" NOTICE: merging multiple inherited definitions of column "name" NOTICE: merging multiple inherited definitions of column "code" NOTICE: merging multiple inherited definitions of column "cost" CREATE TABLE test=# |
Examining the definitions of the tables, note that the inventory table informs us that this table inherits from the product table and has at least one child table. The warehouse table tells us that it inherits from the product and the inventory tables.
1 2 3 4 5 6 7 | test=# d products Table "public.products" Column | Type | Collation | Nullable | Default --------------+-----------------------+-----------+----------+--------- id | integer | | | product_name | character varying(65) | | | price | numeric(9,2) | | | |
1 2 3 4 5 6 7 8 9 10 11 | test=# d inventory Table "public.inventory" Column | Type | Collation | Nullable | Default ----------+---------+-----------+----------+--------- id | integer | | not null | name | text | | | code | integer | | | cost | real | | | quantity | integer | | not null | Inherits: product Number of child tables: 1 (Use d+ to list them.) |
1 2 3 4 5 6 7 8 9 10 11 12 | test=# d warehouse Table "public.warehouse" Column | Type | Collation | Nullable | Default ----------+---------+-----------+----------+--------- id | integer | | not null | name | text | | | code | integer | | | cost | real | | | quantity | integer | | not null | location | integer | | not null | Inherits: product, inventory |
The next step is to add some test data.
1 2 3 | test=# insert into warehouse values (1,'Dohickey',101,1.99,123,1),(2,'Whatsis',17,99.99,8,1),(3,'Thing-e',45,999.99,3,2); INSERT 0 3 test=# |
Each table has its own data. All the product data is in the product table.
1 2 3 4 5 6 7 | test=# select * from products; id | product_name | price ----+--------------+-------- 1 | Table | 150.00 2 | Desk | 100.00 3 | Chair | 75.00 (3 rows) |
And the inventory data is in the inventory table.
1 2 3 4 5 6 7 | test=# select * from inventory; id | name | code | cost | quantity ----+----------+------+--------+---------- 1 | Dohickey | 101 | 1.99 | 123 2 | Whatsis | 17 | 99.99 | 8 3 | Thing-e | 45 | 999.99 | 3 (3 rows) |
1 2 3 4 5 6 7 | test=# select * from warehouse; id | name | code | cost | quantity | location ----+----------+------+--------+----------+---------- 1 | Dohickey | 101 | 1.99 | 123 | 1 2 | Whatsis | 17 | 99.99 | 8 | 1 3 | Thing-e | 45 | 999.99 | 3 | 2 (3 rows) |
If you are in a situation where you need to exclude some users from accessing specific tables, you could use inheritance in place of column permissions or views to segregate the data.
Data propagation
But be careful. If we insert a new record into the product table, the senior parent table, the data does not flow downward to the inventory or warehouse tables.
1 2 3 4 5 6 | test=# insert into products values (444,'four fours',123.45); INSERT 0 1 test=# select * from inventory where id = 444; id | name | code | cost | quantity ----+------+------+------+---------- (0 rows) |
However, the data does not flow logically downward to the warehouse table.
1 2 3 4 | test=# select * from warehouse where id = 444; id | name | code | cost | quantity | location ----+------+------+------+----------+---------- (0 rows) |
Likewise, inserting into the inventory (middle table) does not propagate the data to the parent or its child table.
1 2 3 4 5 6 7 8 9 10 | test=# insert into inventory values (555,'fives',5,0.50,555); INSERT 0 1 test=# select * from products where id = 555; id | product_name | price ----+--------------+------- (0 rows) test=# select * from warehouse where id=555; id | name | code | cost | quantity | location ----+------+------+------+----------+---------- (0 rows) |
If we create a second child table from the inventory table and add data to that new child table, the data will flow up into inventory and product. But none of that data makes it to the warehouse table. So you need to be careful of where you are adding data and aware that siblings and child tables may be deprived of the data you need.
Conclusion
Inheritance can be a useful way to normalize data. It may also be handy in securing data. However, you need to be aware that the data does not propagate by itself through the family tree in the way you expect.
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community in a single distribution, designed and tested to work together.
Download Percona Distribution for PostgreSQL Today!
