Postgres aggregator to find common value or null

Question

I have two tables with a one-to-many relationship that looks something like this:

CREATE TABLE products (
  id UUID PRIMARY KEY,
  name TEXT
);

CREATE TABLE product_variations (
  id UUID PRIMARY KEY,
  product_id UUID REFERENCES products,
  attribute1 TEXT,
  attribute2 TEXT,
  attribute3 TEXT
);

I'd like to create a view which finds all the attributes that are the same for all variations of a product. I'm guessing it would look something like this:

CREATE VIEW product_shared_attributes AS
  SELECT "product_id", some_aggregator("attribute1"), some_aggregator("attribute_2") 
  FROM product_variations
  GROUP BY "product_id";

What kind of aggregator could I use for this? I would expect it to return the value if all values are the same, and NULL otherwise.

Answer 1

You could count the number of distinct values:

CREATE VIEW product_shared_attributes AS
  SELECT product_id,
         CASE WHEN count(DISTINCT attribute1) = 1
              THEN min(attribute1)
         END AS attribute1,
         CASE WHEN count(DISTINCT attribute_2) = 1
              THEN min(attribute_2)
         END AS attribute_2,
  FROM product_variations
  GROUP BY product_id;

You could use max instead of min, but you need an aggregate function. The CASE expression results in NULL if none of the conditions apply and there is no ELSE branch.