Inheritance

I l @ ve RuBoard

PostgreSQL allows tables to inherit properties and attributes from other tables. This is useful in cases in which many tables are needed to hold very similar information. In these cases, it is often possible to create a parent table that holds the common data structures, allowing the children to inherit these structures from the parent.

For instance, let's create a table called employees :

 CREATE TABLE employees (name char(10), salary numeric(9,2)); 

Now you can create a specific table just for cooks , who happen to need all the information that other employees need:

 CREATE TABLE cooks (specialty char(10)) INHERITS(employees);  SELECT * FROM cooks;  name salary specialty  --------------------- (0 rows)  INSERT INTO cooks VALUES ('Bill', 877.50, 'Steak');  SELECT * FROM cooks;  name     salary     specialty  ----------------------------- 'Bill'   877.50     'Steak' 

The real power of inheritance lies in the capability to search parent tables for information stored in child tables, without having to explicitly name the child table in the query.

 SELECT * FROM employees* WHERE name='Bill';  name     salary     specialty  ----------------------------- 'Bill'   877.50     'Steak' 

Notice that the preceding query includes an asterisk ( * ) after the table employees . This is to tell PostgreSQL to extend its search to child tables as well.

Starting in PostgreSQLVersion 7.1, all queries, by default, extend their searches to child tables. There is no need to include the extra asterisk after the table name, although that notational style is still supported.

To limit a query search to a particular table inVersion 7.1, there are two options. One is to set the environmental variable SQL_Inheritance to OFF . The second is to use the keyword ONLY during a SELECT query, for instance:

 SELECT * FROM ONLY employees WHERE name='Bill';  name salary specialty  --------------------- (0 rows) 

Or, alternatively, you can use the SET command:

 SET SQL_Inheritance TO OFF;  SELECT * FROM employees WHERE name='Bill';  name salary specialty  --------------------- (0 rows) 

Although table inheritance is a powerful feature of PostgreSQL, there are some limitations. Namely, the limitation comes in the form of conceptual planning.

Although not a limitation of PostgreSQL per se, unless table inheritance is carefully planned, problems will arise. For instance, in the preceding examples, you are assuming that every cook will also be an employee .

Certainly, it's possible that a new relationship could be formed that would not fall under the category of employee . Perhaps volunteer or consultant would be more appropriate for a given relationship. At this point, your previous database schema is problematic and will need to be redone to fit more accurately. As mentioned earlier, this is not an inherent problem with PostgreSQL; it just underlines the need for careful planning when using inheritance.

I l @ ve RuBoard