Uncategorized

Postgresql table inheritance

Not many people know about one of PostgreSQL’s killer features – table inheritance.

An absolute killer feature of this is to be able to have one table that needs to reference more than one type of entity in different situations. In the previous design of our product (using MySQL, but presented with Pg syntax below) we had:

CREATE TABLE s (
  id SERIAL PRIMARY KEY,
  target_id INTEGER NOT NULL,
  target_type VARCHAR(255) NOT NULL,
  ...
);

Here target_id references two different tables depending on the situation, and target_type is filled in to indicate which table to reference. This is sub-optimal because there’s no referential integrity.

With table inheritance you can do:

CREATE TABLE s (
  id SERIAL PRIMARY KEY,
  ...
);
CREATE TABLE s_a (
  a_id INTEGER NOT NULL REFERENCES a (id) ON DELETE CASCADE
) INHERITS (s);
CREATE TABLE s_b (
  b_id INTEGER NOT NULL REFERENCES b (id) ON DELETE CASCADE
) INHERITS (s);

The beauty of table inheritance is that when we insert into s_a or s_b the rows show up in table s as well. This allows us to have an overall view of everything (in s) and partial views of the data depending on the type of reference in s_a and s_b. Beautiful!

Standard

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s