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.

Complex query. I don't mind disclosing the full version of the query. However, long queries scare of people. I think it makes sense to extract the essence of the question. Here is the query that I ended up with gist.github.com/gajus/a803c6ade49971ccc117da5dec4151c7. — Gajus
– Gajus, Commented May 13, 2018 at 13:01
Sure. It makes a difference for the best solution, though. Is there a separate table with all relevant, unique event_id, like event? — Erwin Brandstetter
– Erwin Brandstetter, Commented May 13, 2018 at 13:06
And I assume you mean "for every event_id that is not already associated with 3D or 2D attribute"? Your expected result suggests as much. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 13, 2018 at 13:09

Erwin Brandstetter · Accepted Answer · 2018-05-13 14:02:39Z

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;

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.

I don't recognize the syntax of TABLE cte. What is that doing? — Noumenon
– Noumenon, Commented May 13, 2018 at 19:34
@Noumenon: Short for SELECT * FROM cte. I added a link to explain above: stackoverflow.com/questions/30275979/… — Erwin Brandstetter
– Erwin Brandstetter, Commented May 14, 2018 at 0:47

Gajus · Accepted Answer · 2018-05-13 11:38:07Z

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.

Gerard H. Pille · Accepted Answer · 2018-05-14 07:06:34Z

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')".

Lennart - Slava Ukraini · Accepted Answer · 2018-05-13 13:22:32Z

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

Stack Exchange Network

How to conditionally append rows to a result set?

4 Answers 4

Linked

Hot Network Questions