PostgreSQL – How to Conditionally Append Rows to a Result Set

postgresql

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.

Best Answer

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:

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:

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

Related Question