If I could name a number one feature I love most about PostgreSQL, it's the table inheritance feature which we described in How to Inherit and Uninherit. A lot of people use it for table partitioning using CONSTRAINT EXCLUSION. Aside from that, in combination with PostgreSQL schema search_path (customizable by user and/or database) it makes for a very flexible abstraction tool. For example, for many of our web apps that service many departments where each department/client wants to keep a high level of autonomy, we have a schema set aside for each that inherits from a master template schema. Each department site uses a different set of accounts with the primary schema being that of the department/client so that they are hitting their own tables.
Inheritance allows us to keep data separate,do roll-up reports if we need to, use the same application front-end, and yet allows us the ability to add new columns in just one place (the master template schema). It is more flexible than other approaches because for example we may have a city organization that need to share tables, like for example a system loaded list of funding source shared across the agency. We can set aside these shared tables in a separate schema visible to all or have some have their own copy they can change if they don't want to use the shared one.
Every once in a while, we find ourselves needing to query the whole hierarchy and needing to know which table the results of the query are coming from. To help solve that issue, we employ the use of the system column tableoid which all user tables have. The tableoid is the the object id of a table. PostgreSQL has many system columns that you have to explicitly select and can't be accessed with a SELECT * with the tableoid being one of them. These are: tableoid, cmax,cmin, xmin,xmax,ctid which are all described in System Columns. The PostgreSQL docs on inheritance have examples of using it, but we thought it worthwile to repeat the exercise since it's not that common knowledge and is unique enough feature of PostgreSQL that others coming from other relational databases, may miss the treat. I've often demonstrated it to non-PostgreSQL users who use for example SQL Server or MySQL, and they literally fall out of their chair when I show the feature to them and its endless possibilities.
For this exercise, we'll use the example from How to Inherit and Uninherit, note that later versions of PgAdmin III, do have this built into the interface, so the short-comings we described in the article don't exist for PgAdmin III 1.10 and above.
First we create the tables and populate them like so:
CREATE SCHEMA micromanagers;
CREATE TABLE micromanagers.timesheet
(
ts_date date NOT NULL,
ts_hours numeric(5,2) NOT NULL DEFAULT 0,
ts_employeename character varying(50) NOT NULL,
CONSTRAINT pk_timesheet PRIMARY KEY (ts_date, ts_employeename)
);
CREATE SCHEMA icecreammakers;
CREATE TABLE icecreammakers.timesheet
( CONSTRAINT pk_timesheet PRIMARY KEY (ts_date, ts_employeename)
) INHERITS (micromanagers.timesheet);
CREATE SCHEMA whitecollar;
CREATE TABLE whitecollar.timesheet
( CONSTRAINT pk_timesheet PRIMARY KEY (ts_date, ts_employeename)
) INHERITS (micromanagers.timesheet);
set search_path=icecreammakers;
INSERT INTO timesheet(ts_date,ts_hours,ts_employeename)
VALUES('2012-01-16', 5, 'rrunner');
set search_path=micromanagers;
INSERT INTO timesheet(ts_date,ts_hours,ts_employeename)
VALUES('2012-01-16', 20, 'wecoyote');
set search_path=whitecollar;
INSERT INTO timesheet(ts_date,ts_hours,ts_employeename)
VALUES('2012-01-16', 7, 'dduck');
Wile E. Coyote is doing his rounds and is particularly interested in the whereabouts of Road Runner who seems to jump around from department to department. He wants to know where Road Runner is hiding out today.
-- Check - let us be a Wiley --
set search_path=micromanagers;
SELECT t.tableoid,n.nspname as timesheet_group
, t.ts_employeename, t.ts_date
FROM timesheet AS t
INNER JOIN pg_class As p ON t.tableoid = p.oid
INNER JOIN pg_namespace As n ON p.relnamespace = n.oid
WHERE t.ts_date = '2012-01-16' AND t.ts_employeename = 'rrunner';
tableoid | timesheet_group | ts_employeename | ts_date
----------+-----------------+-----------------+------------
2332415 | icecreammakers | rrunner | 2012-01-16