POSTGRES ADD COLUMN WITH FOREIGN KEY DRIVERS
POSTGRES ADD COLUMN WITH FOREIGN KEY DRIVER
POSTGRES ADD COLUMN WITH FOREIGN KEY PATCH

POSTGRES ADD COLUMN WITH FOREIGN KEY PATCH

In any case, the model is still more complex due to the presence of a junction table between races and drivers.Īnother important aspect of the array ELEMENT foreign key patch is that the position of the item in the array comes along with implicit – albeit optional – information on ordinality (useful for many use cases). Right, we could have kept the model simpler with just one single table, called race_position and another identifying column for the type of position ( practice1, practice2, etc.). For example, a table called practice1_positions with:

POSTGRES ADD COLUMN WITH FOREIGN KEY DRIVER

For example, the first element in the final_positions array identifies the driver that won that particular race.īefore this patch, we would have had a junction (linking) table to enforce referential integrity.

POSTGRES ADD COLUMN WITH FOREIGN KEY DRIVERS

We have 5 arrays of integers where each element points to a record in the drivers table, and referential integrity is enforced (e.g., we cannot insert in any of those arrays a driver that does not exist). Qualifying_positions integer ELEMENT REFERENCES drivers,įinal_positions integer ELEMENT REFERENCES drivers Practice3_positions integer ELEMENT REFERENCES drivers, Practice2_positions integer ELEMENT REFERENCES drivers, Practice1_positions integer ELEMENT REFERENCES drivers, Current implementation allows only for NO ACTION and RESTRICT actions, even though specific actions have already been considered and will eventually be included in future releases.Ī simple example is probably the easiest way to explain the feature: This feature adds the ELEMENT REFERENCES column constraint, as well as the array ELEMENT table constraint in foreign keys. Array remove and replace functions, for instance, were part of the original patch and have already been included in 9.3. Here we are again, this time with a smaller patch – hoping to improve this feature when (and if) it is accepted in Postgres’ core. This patch is heavily based on the experience we did with the one we submitted for PostgreSQL 9.2 and did not make it. We have submitted a patch for 9.3, but currently it is still missing a reviewer. As you may have guessed, it is not part of the SQL standard. We renamed it “Array ELEMENT foreign keys” – thanks to the feedback received from the hackers list. The Italian team of 2ndQuadrant has been working since last year on adding a new feature to PostgreSQL: support of referential integrity between the elements of an array in a table (referencing) and the records of another table (referenced).

This is another great release for PostgreSQL, but we are already ahead in the development of the next release: PostgreSQL 9.3.

PostgreSQL 9 Cookbook – Chinese EditionĪs my French colleague Dimitri Fontaine was pointing out a few days ago, PostgreSQL 9.2 is out.

PostgreSQL Server Programming Cookbook – 2nd Edition.

PostgreSQL 9 Administration Cookbook – 3rd Edition.PostgreSQL High Availability Cookbook – 2nd Edition.

Postgres add column with foreign key