Postgres Partial Index using foreign column

86 Views Asked by At

Consider the following table with a partial index:

CREATE TABLE triples(
  cardinality text NOT NULL,
  entity_id text NOT NULL,
  attribute text NOT NULL,
  value jsonb NOT NULL,
);

CREATE UNIQUE INDEX triples_ea ON triples(entity_id, attribute) INCLUDE(value) WHERE cardinality = 'one';

Here I've created a unique covering index on entity_id and attribute, based on the cardinality value.

But, what if cardinality is in a different column?

Something like this:

CREATE TABLE schema(
  id text PRIMARY KEY,
  cardinality text NOT NULL,
)
CREATE TABLE triples(
  entity_id text,
  attribute text FOREIGN KEY REFERENCES schema(id),
  value jsonb
);

How could I write something along the lines of, if cardinality is in the referencing table?

CREATE UNIQUE INDEX triples_ea ON triples(entity_id, attribute) INCLUDE(value) WHERE cardinality = 'one';
0

There are 0 best solutions below