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';