Inheritance

With PostgreSQL, a table can inherit from one or more other tables. When performing a query on an inherited table, the query can refer to either all rows of a table, or all rows of a table plus any descendants.

For instance, the author table from TODO: Link to correct part of this book contains an author identification number, a last name, a first name, and an e-mail address. These are the current data values in that table.

Suppose we had a distinguished author table that shows which authors received an award. This table inherits from the author table. Create this table with the following command:

    CREATE TABLE dist_author(award text) 
  INHERITS (author); 
   

When you use the inherit option in your CREATE TABLE command, it causes the child table to inherit from the parent table. For example, our distinguished authors table was created with only one column, the award column. If you display this table, it looks like this:

 
   SELECT * FROM dist_author;

 a_id | lastname | firstname | email | award
------+----------+-----------+-------+-------

(0 rows)
   

As you can see, even though we specified to create only one column, the distinguished authors table inherited all of the columns that were originally in the author table. If we inserted values into the distinguish authors table that has columns which correspond to the author table, then those columns are also inserted into the author table. Use the following command to insert into distinguished author some values:

        INSERT INTO dist_author 
        VALUES (110, 'Seuss', '', 
               'seuss@childprodigy.com', 'National Book Awards');
   

Insert another row:

        INSERT INTO dist_author 
        VALUES (231, 'Alcott', 'Luoisa May'
               'lalcott@bestbooks.com', 'Nobel Laureate');    
   

When you inserted these values into the distinguished authors (child) table, it also inserted these values into the author (parent) table. But if you insert a value into the author table, then only that table will contain the newly inserted data. This rule also applies to an UPDATE command. The only exception is when a deletion is performed.

It is important to understand which tables are affected by a command. The table below roughly summarizes typical commands used on a table and how those commands will affect tables involved in inheritance:

	NULL
	If you perform an update on a child table and do not specify a value for columns that correspond to the parent table, then those columns are assigned a NULL value. Although there is nothing in that row, a NULL record is still inserted into the parent table. If you delete NULL rows from a parent table, then that same record in the child table is also automatically deleted.

To view a table without its inherited rows, use the ONLY option. You can use this ONLY option whenever performing an UPDATE, SELECT, or DELETE. The command below only views the authors who has not received an award for their work:

            SELECT lastname, firstname 
              FROM ONLY author;
   

The output for this command is:

 lastname |  firstname
----------+-------------
 Brown    | Margaret W.
 Burgeous | Paulette
 Williams | Margary
(3 rows)