1

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';
Stepan Parunashvili
  • 2,627
  • 5
  • 30
  • 51
  • 1
    Not possible. An index can only contain data from **one** table. –  Nov 09 '22 at 15:10
  • Ah, thank you @a_horse_with_no_name ! If you like feel free to answer and resolve the question – Stepan Parunashvili Nov 09 '22 at 15:15
  • 1
    You want a multi-table index. I've only seen true multi-table indexes in Oracle. Alternatively, you can index a materialized view; however, this is a whole different ball game, and you'll need decide how to manage stale data and refresh events. – The Impaler Nov 09 '22 at 15:16

0 Answers0