2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Gabriele's PlanetPostgreSQL3 / PostgreSQL 9.3 development: Array ELEMENT Foreign Keys
Gabriele Bartolini

PostgreSQL 9.3 development: Array ELEMENT Foreign Keys

September 18, 2012/19 Comments/in Gabriele's PlanetPostgreSQL /by Gabriele Bartolini

As my French colleague Dimitri Fontaine was pointing out a few days ago, PostgreSQL 9.2 is out. This is another great release for PostgreSQL, but we are already ahead in the development of the next release: PostgreSQL 9.3.

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).

We renamed it “Array ELEMENT foreign keys” – thanks to the feedback received from the hackers list. As you may have guessed, it is not part of the SQL standard. We have submitted a patch for 9.3, but currently it is still missing a reviewer.

This patch is heavily based on the experience we did with the one we submitted for PostgreSQL 9.2 and did not make it. Here we are again, this time with a smaller patch – hoping to improve this feature when (and if) it is accepted in Postgres’ core. Array remove and replace functions, for instance, were part of the original patch and have already been included in 9.3.

This feature adds the ELEMENT REFERENCES column constraint, as well as the array ELEMENT table constraint in foreign keys. 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.

A simple example is probably the easiest way to explain the feature:

[sql]
CREATE TABLE drivers (
driver_id integer PRIMARY KEY,
first_name text,
last_name text,
…
);

CREATE TABLE races (
race_id integer PRIMARY KEY,
title text,
race_day DATE,
…
practice1_positions integer[] ELEMENT REFERENCES drivers,
practice2_positions integer[] ELEMENT REFERENCES drivers,
practice3_positions integer[] ELEMENT REFERENCES drivers,
qualifying_positions integer[] ELEMENT REFERENCES drivers,
final_positions integer[] ELEMENT REFERENCES drivers
);
[/sql]

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). For example, the first element in the final_positions array identifies the driver that won that particular race.

Before this patch, we would have had a junction (linking) table to enforce referential integrity. For example, a table called practice1_positions with:

  • race_id (FK)
  • position
  • driver_id (FK)

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.). In any case, the model is still more complex due to the presence of a junction table between races and drivers.

Another 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). Constraints on the cardinality of the array can be applied too, forcing for example the number of elements (consider the starting lineup of a football/soccer team, which has 11 available slots).

However, what I like the most of this model though is that it allows developers to think in terms of object aggregation even at database level, implementing many-to-many relationships without the need of linking tables – while keeping the same requirements of logical data integrity and consistency.

Apart from the tough challenge of being accepted in the core of PostgreSQL, it would be interesting to see how this feature is taken into consideration by ORMs – but I guess this is just speculation as things stand now.

In any case, I take the chance to invite everyone that wants to contribute to PostgreSQL to join the current commit fest and become a reviewer for this patch.

A very useful resource for new (not only) reviewers is the Wiki of PostgreSQL.

Tags: array element foreign keys, core development, development, element references, postgres 9.3, postgresql 9.3
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
19 replies
  1. Username*
    Username* says:
    September 25, 2012 at 11:55 am

    I strongly appreciate the inclusion of this ELEMENT REFERENCES constraint into the PostgreSQL core. Just because we need it. Normalising is not an option in our case since it concerns several dozens of multi-valued attributes which are list (catalogue) values referenced by integer ids (soil database). Normalising would simply blow our scheme.
    (We created thirty or so environment databases without making use of arrays. But this one is different.)

    Matthias

    Reply
  2. Matheus de Oliveira
    Matheus de Oliveira says:
    October 1, 2012 at 2:09 pm

    Great functionality…

    Would that work with multi-columns primary keys?

    Another syntax I think would be great, something like:

    CREATE TABLE races (
    race_id integer PRIMARY KEY,
    title text,
    race_day DATE,
    …
    practice1_positions drivers[]
    …
    );

    Or something like it would be also great. And a “JOIN FETCH”-like ORMs to join all.

    Reply
  3. jney
    jney says:
    November 19, 2012 at 4:11 pm

    very cool feature !
    what’s the syntax using `ALTER TABLE` ?
    something like this ?
    `alter table posts add constraint posts_tags_fk foreign key (tag_ids) element references tags(id);` ?
    can’t wait february 2013 to test it

    Reply
  4. Glyn Astill
    Glyn Astill says:
    February 13, 2013 at 12:56 pm

    Great, this would be a very useful feature!

    Reply
  5. Stephan Doliov
    Stephan Doliov says:
    April 26, 2013 at 10:04 pm

    I’d love this feature; I find that this type of a feature can bridge the divide between the mature SQL relational database approaches and the emerging MongoDB types of software. In the webapp world that I live in, the handiest way to exchange data between server and client is as JSON objects, yet the handiest way to enforce referential integrity is to use a relational db. So this type of feature would be a huge win for me. Spread the word!

    Reply
  6. Elwetana
    Elwetana says:
    June 26, 2013 at 1:14 pm

    I also think this would be a very useful features, however looking at the documentation for versions 9.3 and 9.4 it looks like it was not included. Is there any update on the progress of accepting the patch into the PostgreSQL mainline? Thanks.

    Reply
  7. Joe Van Dyk
    Joe Van Dyk says:
    July 3, 2013 at 5:59 am

    Looks like this won’t make it into 9.3. Really hoping it can in 9.4!

    Reply
  8. John Fawcett
    John Fawcett says:
    August 17, 2013 at 1:03 am

    Unfortunately, this doesn’t look to be in the 9.4 roadmap either :/ http://wiki.postgresql.org/wiki/Todo#Arrays

    It’s already possible to use arrays as junction tables as I outline in my blog post http://blog.j0.hn/post/57492309635/using-arrays-as-relation-tables-in-postgres

    But you have to use triggers to enforce foreign keys. No bueno. It looks like we might have to wait until 9.5 to get array foreign key referencing.

    Reply
  9. ppeess
    ppeess says:
    November 15, 2013 at 6:44 pm

    Hi,

    how to install this patch on Postgresql 9.3 (Ubuntu 11.4), some instruction?

    Reply
  10. Golgote
    Golgote says:
    January 31, 2014 at 2:18 pm

    Just today, I needed this feature for an application I am developing and I am sad it didn’t make it into 9.3.
    It would be a very useful addition and will make ARRAYs really interesting, especially in these troubled NoSQL times… I guess I will have to live without integrity for a while, or maybe rely on triggers.

    Reply
  11. Karlodun
    Karlodun says:
    March 3, 2016 at 4:08 pm

    I also need this feature for my projects. However the patch seems to stay out of interest….
    Any way for us to help?

    Reply
  12. JingAn Chen
    JingAn Chen says:
    August 5, 2016 at 2:32 am

    Just want to know what’s the status of this feature? Any update?

    Reply
  13. pankleks
    pankleks says:
    December 8, 2016 at 10:57 pm

    Guys any update on this. I love PG, and this is currently top 1 of my missing features.
    Any news if it’s going to be included?

    Reply
    • craig.ringer
      craig.ringer says:
      January 12, 2017 at 11:13 am

      It won’t. Performance concerns led to the feature not making the cut for 9.4, and there’s nobody currently working on it.

      Reply
  14. Pavel Alexeev
    Pavel Alexeev says:
    June 14, 2017 at 12:50 pm

    Why it may be incorporated into Postgres with documentation what implementation is not so bleeding fast?

    Reply
    • craig.ringer
      craig.ringer says:
      October 13, 2017 at 7:42 am

      Because the performance impact went beyond users of the feature, and into other parts of the system.

      Reply
  15. H. Guijt
    H. Guijt says:
    August 29, 2017 at 3:46 pm

    This would be very useful to me. Right now I use triggers and an N-M table to enforce referential integrity, but a direct implementation in the database would be a lot easier to manage. Is there any news on this feature? Was it included in Postgres 9.5 or 10?

    Reply
    • craig.ringer
      craig.ringer says:
      October 13, 2017 at 7:27 am

      It was not included in Pg 9.5 or 10, and as far as I know nobody has picked it up to work on it.

      Reply
  16. pankleks
    pankleks says:
    October 23, 2017 at 9:37 pm

    That’s extremely disappointing – I understand that this kind of FK can possibly have negative performance impact. However in the end it’s user decision to use it or not.

    How one can influence Postgres team to pick-up this feature? Judging by no of question’s related to that it seams like popular request among community.

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Managing useful clusters with repmgr CTE and the Birthday Paradox
Scroll to top
×