On Thu, 31 Oct 2019 at 14:50, Stephen Frost <sfr...@snowman.net> wrote:
> Greetings,
>
> * Peter Geoghegan (p...@bowt.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.
