How to conditionally append rows to a result set?

Question

I have a complex SELECT query table that identifies relationship between event_id and attribute. Here is a simplification with a VALUES expression:

SELECT event_id, attribute
FROM (
  VALUES
    (1, '2D'),
    (1, 'IMAX'),
    (2, 'IMAX'),
    (3, '3D')
) event_attribute (event_id, attribute)

I want to include an extra (event_id, '2D') record for every event_id that is not already associated with a 3D or 2D attribute. How to conditionally append rows to a result set?

In case of the above table, the expected result would be:

(1, '2D'),
(1, 'IMAX'),
(2, 'IMAX'),
(2, '2D'),
(3, '3D')

There is also a table event with one row for each relevant id.

Answer 1

Assuming (like was added later) a separate table event with all relevant unique id - this helps performance a bit:

WITH cte(event_id, attribute) AS (
   -- big SELECT query goes here instead of the VALUES expression
   VALUES
    (1, '2D'),
    (1, 'IMAX'),
    (2, 'IMAX'),
    (3, '3D')
   )
TABLE cte
UNION ALL
SELECT e.id, '2D'
FROM   event e
LEFT   JOIN cte ON cte.event_id = e.id
               AND cte.attribute IN ('2D','3D')
WHERE  cte.event_id IS NULL;

Related:

• Select rows which are not present in other table
• Optimize GROUP BY query to retrieve latest record per user
• Is there a shortcut for SELECT * FROM?

If your query only returns a subset of all event_id, you can't use the table event to simplify like this. Alternative without table event:

WITH cte AS (
   -- big SELECT query goes here instead of the VALUES expression
   VALUES
    (1, '2D'),
    (1, 'IMAX'),
    (2, 'IMAX'),
    (3, '3D')
   )
TABLE cte
UNION ALL
SELECT event_id, '2D'
FROM   cte
GROUP  BY 1
HAVING count(*) FILTER (WHERE attribute IN ('2D', '3D')) = 0;

This is somewhat similar to what you answered yourself, just shorter and a bit faster. In particular the aggregate FILTER clause should be instrumental. Related:

• For absolute performance, is SUM faster or COUNT?

Since there are no indexes on the derived table from the CTE, the second query may be faster to begin with.

Answer 2

cfr. http://sqlfiddle.com/#!17/82868/6

create table t as SELECT
  event_attribute.event_id,
  event_attribute.attribute
FROM (
  VALUES
    (1, '2D'),
    (1, 'IMAX'),
    (2, 'IMAX'),
    (3, '3D')
) event_attribute (event_id, attribute);


select E, '2D' a from (
  select distinct event_id E from t
   where not exists (
     select null from t t1
      where t1.event_id = t.event_id and t1.attribute = '3D')) X;

Probably faster for larger datasets:

select E, '2D' a from (
  select distinct event_id E from t
  except
  select distinct event_id E from t
   where attribute = '3D') T2;

see http://sqlfiddle.com/#!17/82868/14

As, per the edited question, it's better to avoid duplicate '2D' entries, "attribute = '3D'" should be replaced by "attribute in ('3D','2D')".

Answer 3

For the record, this is what I have come up with:

WITH
  event_attribute AS
  (
    SELECT
      event_attribute.event_id,
      event_attribute.attribute
    FROM (
      VALUES
        (1, '2D'),
        (1, 'IMAX'),
        (2, 'IMAX'),
        (3, '3D')
    ) event_attribute (event_id, attribute)
  ),
  append_attribute_event AS
  (
    SELECT event_id
    FROM event_attribute
    GROUP BY event_id
    HAVING
      MAX(CASE "attribute" WHEN '2D' THEN 1 ELSE 0 END) = 0 AND
      MAX(CASE "attribute" WHEN '3D' THEN 1 ELSE 0 END) = 0
  )
SELECT * FROM event_attribute
UNION ALL
SELECT event_id, '2D' "attribute"
FROM append_attribute_event

However, I am pretty sure Gerard's suggestion is better.

Answer 4

A straight forward solution would be:

select event_id, attribute
from event_attribute
union all
select event_id, '2D' 
from event_attribute t1 
where not exists (
    select 1 from event_attribute t2 
    where t1.event_id = t2.event_id 
      and t2.attribute in ('2D','3D')
);