我不确定我是否完全理解你想要的。我添加了一个“playstation 4”行，它不是main_product，所以它的product_launch值没有改变。如果这不是你想要的，其他答案可能更容易。

查询#1

SELECT p.id, p.name, p.family_id, p.enabled
     , CASE
         -- We're on the family's main product, so we can check if any other products in the family are "product_launch"
         WHEN p.id = f.main_product_id THEN bool_or(product_launch) OVER (PARTITION BY family_id)
         ELSE product_launch
       END AS product_launch
     , f.id, f.name, f.main_product_id
from products as p
left outer join families as f
  on p.family_id = f.id
ORDER BY family_id, p.id;

View on DB Fiddle
编辑：
更新答案以从最终选择中删除不需要的行：

SELECT *
FROM (
    SELECT p.id, p.name, p.family_id, p.enabled
         , CASE
             -- We're on the family's main product, so we can check if any other products in the family are "product_launch"
             WHEN p.id = f.main_product_id THEN bool_or(product_launch) OVER (PARTITION BY family_id)
             ELSE product_launch
           END AS product_launch
         , f.id AS f_id, f.name, f.main_product_id
    from products as p
    left outer join families as f
      on p.family_id = f.id
) AS sub
WHERE "enabled"
  AND ((id = main_product_id)   OR (main_product_id is null))

我不知道这是不是你想要的，但试试这个：

WITH familes_with_one_product_launched AS
(
  SELECT 
      DISTINCT family_id 
  FROM products 
  WHERE
      product_launch = TRUE
), main_products AS
(
    SELECT 
        p.id 
    FROM products as p 
    INNER JOIN families AS f ON (f.main_product_id = p.id)
)
SELECT
    id, name, family_id, enabled,
    CASE WHEN is_main_product AND family_has_launched_product 
        THEN TRUE
        ELSE product_launch
    END AS product_launch
FROM
(
  SELECT 
      p.*,
      CASE WHEN mp.id IS NULL THEN FALSE ELSE TRUE END AS is_main_product,
      CASE WHEN fl.family_id IS NULL THEN FALSE ELSE TRUE END AS family_has_launched_product
  FROM products AS p
      LEFT JOIN main_products AS mp ON (p.id = mp.id)
      LEFT JOIN familes_with_one_product_launched AS fl ON (p.family_id = fl.family_id)
) AS x

原来的表设计有一些问题，最麻烦的是 families 和 products 是相互引用的，从 families 到 products 的外键关联没有在DDL中声明这种设计所导致的问题之一是无法使用约束来确保 families.main_product_id 和 products. families_id 相互一致。我通过从 families 中删除 main_product_id 并向 products 添加布尔标志 is_main_product 来重构表。部分唯一索引强制约束每个家族只能有一个主产品。
我还做了以下与原始问题无关的更改，但改进了模型：
1.为 name 列添加了唯一约束。
1.将布尔列NOT NULL设置为默认值。
对布尔列的更改有助于避免在计算逻辑表达式时与处理NULL相关的额外复杂性。
以下是修改后的表创建语句：

CREATE TABLE families(
  id serial PRIMARY KEY,
  name varchar(50) UNIQUE NOT NULL
);

CREATE TABLE products(
  id serial PRIMARY KEY,
  name varchar(50) UNIQUE NOT NULL,
  family_id int REFERENCES families(id),
  enabled boolean NOT NULL DEFAULT FALSE,
  product_launch boolean NOT NULL DEFAULT FALSE,
  is_main_product boolean NOT NULL DEFAULT FALSE,
  -- a main product must have a family
  CONSTRAINT products_ck CHECK (family_id IS NOT NULL OR is_main_product = FALSE)
);

-- a family can have only one main product
CREATE UNIQUE INDEX products_main_family_product_idx ON products(family_id)
  WHERE is_main_product = TRUE;

请注意，使用索引强制执行一个系列只能有一个主要产品的规则会导致一个问题：更改主产品需要两次更新，一次是将当前主产品的 is_main_product 设置为false，另一次是将新主产品的 is_main_product 设置为true。由于两次更新都可以在同一事务中发生，因此这应该是一个小小的不便。
我修改了insert，以消除对生成的 id 值的显式依赖。这有助于确保 id 值和相关序列同步。

INSERT INTO families(name)(
  VALUES ('macbooks'),
         ('consoles'));

WITH p(
  name,
  family_name,
  enabled,
  product_launch,
  is_main_product
) AS (
  VALUES ('playstation 5', 'consoles', TRUE, TRUE, FALSE),
         ('playstation 3', 'consoles', TRUE, FALSE, TRUE),
         ('xbox 360', 'consoles', TRUE, FALSE, FALSE),
         ('mac book 2015', 'macbooks', TRUE, FALSE, FALSE),
         ('mac book 2018', 'macbooks', TRUE, FALSE, FALSE),
         ('Coca cola', NULL, TRUE, FALSE, FALSE),
         ('Pepsi', NULL, TRUE, FALSE, FALSE))
INSERT INTO products(name, family_id, enabled, product_launch, is_main_product)
SELECT
  p.name,
  f.id,
  p.enabled,
  p.product_launch,
  p.is_main_product
FROM
  p
  LEFT JOIN families f ON f.name = p.family_name;

以下查询返回的输出与原始问题中发布的示例一致：

WITH t AS (
  SELECT
    p.id,
    p.name,
    p.family_id,
    p.enabled,
    -- product_launch is true if any product in the family is the main or a launched product
    BOOL_OR(p.is_main_product OR p.product_launch) OVER (PARTITION BY p.family_id) AS product_launch,
    f.name AS family_name,
    MAX(p.id) FILTER (WHERE p.is_main_product) OVER (PARTITION BY p.family_id) AS main_product_id
  FROM
    products AS p
    LEFT OUTER JOIN families AS f ON p.family_id = f.id
  WHERE
    p.enabled
)
SELECT
  t.*
FROM
  t
WHERE
  -- return only the main product if one is defined for the family
  t.id = COALESCE(t.main_product_id, t.id)
ORDER BY
  t.family_id DESC NULLS LAST,
  t.id;