A lot of this information is nicely tucked away in the PostgreSQL docs in http://www.postgresql.org/docs/8.3/interactive/ddl-inherit.html,
but since the docs are so huge and rich, one may tend to miss
these things.
While there are numerous interesting use cases for the PostgreSQL inheritance structure, one of the key reasons people use it is for table partitioning strategies.
How do you make a stand-alone table a child of another table?
The first question that comes to mind is why would you ever need a table to adopt another table. There are 2 reasons that come to mind.
- When you are loading huge amounts of data especially of a read only nature - its often convenient to not have that table be visible to your applications until
you are done with the loading process. So you may want to make it a child after the loading.
- Your tables seemed fairly unrelated when you started out and then one day you realized you really were talking about apples and apples and need to report on them together at a higher level.
One situation like this to give a somewhat real-world perspective - lets say you developed a timesheet app for an organization and each department insisted on having their own version of the app and each along with
the basic fields needed to track some additional ones of their own. Then higher forces
came in and said I need to know what everyone is doing, but I don't need to see all that other crap they keep track of.. Two options come to mind - create a bunch of views
that union stuff together or institute a round-up-the-children-and-adopt-them program.
This fits into one of the categories of things that PostgreSQL lets you do that PgAdmin III doesn't have a graphical way to let you do it.
If you try to inherit in PgAdmin III from a table that already exists, that option is just greyed out. So you have to resort to DDL SQL statements. Luckily its fairly trivial. Well this really only works
for PostgreSQL 8.2+. I don't think PostgreSQL 8.1 and below supported INHERIT/NO INHERIT in the ALTER TABLE statement.
--Before Micromanagers
CREATE TABLE icecreammakers.timesheet
(
ts_date date NOT NULL,
ts_hours numeric(5,2) NOT NULL DEFAULT 0,
ts_icecreameaten character varying(50),
ts_employeename character varying(50) NOT NULL,
CONSTRAINT pk_timesheet PRIMARY KEY (ts_date, ts_employeename)
);
--Micromanager comes on the scene
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)
)
WITH (OIDS=FALSE);
ALTER TABLE icecreammakers.timesheet INHERIT micromanagers.timesheet;
--Micromanagers still can't read the information
--unless we give them read-rights into the child tables
--this has some interesting use cases as well
--for implementing row level security
GRANT SELECT ON TABLE icecreammakers.timesheet TO micromanagers;
--Micromanagers discover that icecreammakers are
--more efficient than other employees
--they start to encourage eating of icecream during work
-- and then to keep track of which icecream flavors employees are eating
ALTER TABLE micromanagers.timesheet ADD COLUMN ts_icecreameaten character varying(50);
--Note the message you get when doing this
--NOTICE: merging definition of column "ts_icecreameaten" for child "timesheet"
--Ice cream makers union goes on strike
--Ice cream production goes down - other employees unhappy
--Ice cream makers win to put in their contract
--not to be spied on by micro managers
ALTER TABLE icecreammakers.timesheet NO INHERIT micromanagers.timesheet;
PostGIS in Action
About the Authors
Consulting
Tracked: Jan 16, 06:05