When using table inheritance, the primary keys are not shared among the parent and child tables. This is easy to remedy, if that is the behavior you want to enforce.

First create the parent table using a SERIAL as its primary key.

   create table media ( mid SERIAL PRIMARY KEY, mtype text );

Then for each child table created, alter the table to add the default to the primary key field to be the same as the default value of the parent table primary key field. Since the primary key for the parent table is a serial, the default value for a child table is the next value in the same serial. In this example it is: nextval( 'media_mid_seq' ) This ensures uniqueness among the keys in all of the tables, but does not create a primary key index on the tables.

In order to create indexes on the primary key, it is necessary to simply add a new index to each of the child tables as well as the parent table if necessary. In our example, we plan to have the parent table empty so no additional index is needed for that.

This is the table creation for the child tables along with the ALTER TABLE and CREATE INDEX statements necessary for having a common key and indexes on each table. The full code is also available here.

   create table movies
   ( title text,
     director text,
     producer text,
     movie bytea,
     year integer CHECK (year > 1900 )
    )
   INHERITS  (media) ;
   alter table movies alter column mid set default nextval('media_mid_seq');
   create unique index movie_pk on movies (mid);
   
   create table images
   ( artist text,
     title text,
     image bytea
   )
   INHERITS (media);
   alter table images alter column mid set default nextval('media_mid_seq');
   create unique index images_pk on images (mid);
   
   create table music
   ( artist text,
     album text,
     song  text,
     music bytea
   )
   INHERITS (media);
   alter table music alter column mid set default nextval('media_mid_seq');
   create unique index music_pk on music (mid);

Now suppose you wanted to track several different kinds of meta data for the media elements as a whole. You would create a meta table of sorts and include the primary key of media as foreign key.

create table media_sales
(
  mid integer PRIMARY KEY references media(mid),
  mtype text,
  startd date,
  endd date,
  price numeric,
  sold integer
);

But unfortunately that does not work. This could be arguably a bug or a feature. References are not working on table hierarchies. References only work on the exact specific table referenced. In this case, we have no actual rows in the media table because the rows are all stored in the child tables. You might, arguably correctly, assume that a reference to a column in the parent table includes all of the children tables since a select col from parent would include all of the common columns from all of the rows in the children. But unfortunately, REFERENCES media(mid) is actually REFERENCES ONLY media(mid).

To work around this bug or feature, we will need to create our own referential integrity trigger. Since the select col from parent works as we expect it to we can create the following simple trigger function and attach it to the media_sales table.

   create or replace function rfi_media()
   returns TRIGGER AS
   $$
   DECLARE
   BEGIN
   IF NEW.mid IN (select mid from media) THEN
      RETURN NEW;
   ELSE
      RAISE EXCEPTION 
      'insert or update on table "%" violates foreign key constraint for media table',TG_RELNAME;
   END IF;
   END;
   $$ language 'plpgsql';
   
   drop trigger rfi_media_trig ON media_sales;
   create trigger rfi_media_trig BEFORE INSERT OR UPDATE ON media_sales
   FOR EACH ROW
   EXECUTE PROCEDURE rfi_media();

In this case, any insert or update to the media_sales table will ensure that the mid column is an element of the media table hierarchy.

Contributors: elein at varlena.com

Trapping Errors in plpgsql Ignoring Duplicate Inserts 17-Apr-2005

I have a situation where I parse an apache log file and extract IP addresses and their hosts. This is done daily in a batch job. I store the IPs in a table, adding more each time a new section of the log is parsed.

   create table ip_hosts (
        ip      text PRIMARY KEY,
        host    text
   );

Adding to the list is tricky because I may be inserting an IP that I've already saved to the list. I could query the table and find out if it exists and then insert it but that takes two queries. I could remove the PRIMARY KEY from ip and simply store duplicates, but I use the table with joins and duplicates would be incorrect. It seems simplest in this case to ignore the duplicates.

Duplicates raise the exception UNIQUE_VIOLATION. This aborts the transaction making the duplicates difficult to ignore especially if they are done in block transactions. What I want to do is to catch the raised exception and downgrade it from an error to a warning.

Functions written in plpgsql can now do this. The structure of the statement is like a conditional or try-catch statement:

   BEGIN
      do something...
   EXCEPTION 
      WHEN condition [OR condition ] THEN
         do something else...
      WHEN condition [OR condition ] THEN
         do something really different...
   END

If the statements between BEGIN and EXCEPTION fail, they are rolled back and then the statements after the THEN are executed if and only if they meet the condition stated. This example below has only one condition, but you can have many. Statements before the BEGIN and after the END are unaffected.

The conditions must be one (or more) of the conditions listed in Appendix A of the PostgreSQL manual.

I've created a function to try the insert and if a unique constraint was violated to raise a warning and continue execution. This function is called instead of an INSERT statement by the program that parsed the original log. It returns the number of rows affected.

   CREATE OR REPLACE FUNCTION insert_ips( ip text, host text)
   RETURNS integer AS
   $$
   DECLARE
      rcount integer;
   BEGIN
   BEGIN
   INSERT INTO ip_hosts VALUES (ip, host);
   EXCEPTION WHEN UNIQUE_VIOLATION THEN
           RAISE NOTICE 'Duplicate Key ''%'' for ''%'' ignored.', ip, host;
   END;
   GET DIAGNOSTICS rcount = ROW_COUNT;
   RETURN rcount;
   END
   $$ LANGUAGE 'plpgsql';

Contributors: elein at varlena.com