Re: Proposal: Global Index

On Thu, 31 Oct 2019 at 14:50, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Peter Geoghegan (pg(at)bowt(dot)ie) wrote:
>
[....]

>
> Absolutely- our lack of such is a common point of issue when folks are
> considering using or migrating to PostgreSQL.
>

Not sure how similar my situation really is, but I find myself wanting to
have indices that cross non-partition members of an inheritance hierarchy:

create table t (
id int,
primary key (id)
);

create table t1 (
a text
) inherits (t);

create table t2 (
b int,
c int
) inherits (t);

So "t"s are identified by an integer; and one kind of "t" has a single text
attribute while a different kind of "t" has 2 int attributes. The idea is
that there is a single primary key constraint on the whole hierarchy that
ensures only one record with a particular id can exist in all the tables
together. I can imagine wanting to do this with other unique constraints
also.

At present I don't actually use inheritance; instead I put triggers on the
child tables that do an insert on the parent table, which has the effect of
enforcing the uniqueness I want.