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:
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
To work around this bug or feature, we will need to create our own
referential integrity trigger. Since the
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.
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';
Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009