Re: [GENERAL] referential integrity between elements of an array and another table?

Henry M <henrymanmail@gmail.com> writes:
> No hesitation. I am just trying to understand the options. This blog seemed
> to indicate the functionality existed but it looks like it was never
> completed (
> https://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/
> ).

Yeah, there was a patch like that submitted, but we couldn't come to
consensus on how the feature should behave.  [ digs in archives... ]
See these threads:

https://www.postgresql.org/message-id/flat/4EB3DF02.4020604%402ndQuadrant.it
https://www.postgresql.org/message-id/flat/1343842863.5162.4.camel%40greygoo.devise-it.lan

> I see in this document
> https://www.postgresql.org/docs/9.6/static/sql-createtrigger.html that
> postgresql uses triggers to implement foreign keys.... so I am probably
> just missing the syntactic sugar for arrays. I will try to use a trigger.

It's not just a matter of syntactic sugar, there are some pretty basic
questions about what the semantics ought to be.  We also ran up against
implementation/performance problems that we couldn't find passable
solutions to.  Now, a lot of those problems might not apply to a
single-use application that doesn't have to worry about questions like
cross-type comparison semantics; but that's why you don't see such a
feature in current Postgres.

            regards, tom lane