chilts.org
Blog feed: RSS Feed Icon

Using Base Tables in PostgreSQL

Sometimes object inheritance gets in the way, sometimes it helps. In this case, a small bit of inheritance for tables in Postgres really helps.

Let's say you require all of your tables to contain columns of the same name and type. As an example, in a number of my Postgres databases, I like to have an inserted timestamp field on every table. This means I know exactly when that record was added - it's like a poor-man's audit.

Therefore, instead of adding the inserted column to every table, like this:

 CREATE TABLE news (
    name            TEXT NOT NULL PRIMARY KEY,
    title           TEXT NOT NULL,
    article         TEXT NOT NULL,
    inserted        TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
 );

You can try something like this:

 CREATE TABLE base (
    inserted        TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
 );

then:

 CREATE TABLE news (
    name            TEXT NOT NULL PRIMARY KEY,
    title           TEXT NOT NULL,
    article         TEXT NOT NULL,
    LIKE base INCLUDING DEFAULTS
 );

Of course, in this particular case, you don't save a lot of typing, but imagine if you had a 10, 20 or even hundreds of tables. You get the idea, but don't stop there. Here's another base table I use which also has a column for updated:

 CREATE TABLE base (
    inserted        TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated         TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
 );

Obviously, at the moment, this is exactly the same as the inserted column, but you'll see in the next article how this can be used to show the last time this record was updated.

This post originated on http://chilts.org/.

Email me on andychilton -at- gmail -dot- com.

Published

12 August 2006

Tags