I have a PostgreSQL query for a time series graph with an inner join. A list of tags is used as a where condition for the inner join. I use union all to get a result table with data from two cases: a query with tags in the list and a query with an empty tag list. I want rows with null values from the first select before the union all if the tag list is empty but PostgreSQL returns SELECT 0 i.e. no rows at all. I want null rows because without them I can't programmatically separate the cases.
How can I get a result with null rows? Or is there a way of obtaining the result only from the select before union all if the tag list is not empty and the result only from the select after the union all if the tag list is empty? If it is not possible to achieve any of this, then suggestions about how to extract the data of the two cases from the current query or a modification of it are very welcome. There is no need for handling cases where a restaurant has multiple tags: either a restaurant has one tag or it does not have any tags.
Examples of the query, tables with dummy values and comments here: db<>fiddle.
-- I want null values from this first SELECT before UNION ALL unless
-- a more elegant/efficient solution is possible. But I get 'SELECT 0'.
SELECT
f.days AS days,
SUM (f.waste_kgs) AS waste,
SUM (f.prepared_kgs) AS prepared,
f.waste_type_id AS waste_id,
f.restaurant_id AS restaurant
FROM food f
INNER JOIN tags t
ON f.restaurant_id = t.restaurant_id
WHERE t.name = ANY (array[]::text[]) -- Empty tag list as an argument.
AND f.days BETWEEN '2000-1-1' and '2099-1-1'
AND f.restaurant_id = ANY (array[1, 2, 3])
GROUP BY days, restaurant, waste_id
-- Separately running the first SELECT returns
-- days waste prepared waste_id restaurant
-- SELECT 0
UNION ALL
-- The same query without the INNER JOIN and tag list argument.
SELECT
f.days AS days,
SUM (f.waste_kgs) AS waste,
SUM (f.prepared_kgs) AS prepared,
f.waste_type_id AS waste_id,
f.restaurant_id AS restaurant
FROM food f
WHERE f.days BETWEEN '2000-1-1' and '2099-1-1'
AND f.restaurant_id = ANY (array[1, 2, 3])
GROUP BY days, restaurant, waste_id;
-- The whole query returns
-- days waste prepared waste_id restaurant
-- 2023-01-01 2.5 16.0 1 1
-- 2023-01-02 8.6 7.3 1 2
-- 2023-01-03 10.5 1.8 1 3
-- 2023-01-03 0.8 0.0 2 3
-- SELECT 4
-- No null values to differentiate the empty tag list argument case.
Here are the tables and values from db<>fiddle used in the examples.
CREATE TABLE restaurants (
id int PRIMARY KEY,
name text,
type text
);
CREATE TABLE food (
id int PRIMARY KEY,
restaurant_id int REFERENCES restaurants (id),
waste_type_id smallint NOT NULL,
product_id int NOT NULL,
waste_kgs decimal NOT NULL,
prepared_kgs decimal NOT NULL,
customers smallint NOT NULL,
days date NOT NULL
);
CREATE TABLE tags (
id int PRIMARY KEY,
restaurant_id int REFERENCES restaurants (id),
name text
);
INSERT INTO restaurants VALUES
(1, 'restaurant_1'),
(2, 'restaurant_2'),
(3, 'restaurant_3');
INSERT INTO food VALUES
(1, 1, 1, 1, 1.7, 8.0, 96, '2023-1-1'),
(2, 1, 1, 10, 0.5, 7.0, 96, '2023-1-1'),
(3, 1, 1, 15, 0.3, 1.0, 96, '2023-1-1'),
(4, 2, 1, 12, 7.0, 0.8, 39, '2023-1-2'),
(5, 2, 1, 10, 1.1, 5.0, 39, '2023-1-2'),
(6, 2, 1, 11, 0.5, 1.5, 39, '2023-1-2'),
(7, 3, 1, 8, 10.0, 0.3, 97, '2023-1-3'),
(8, 3, 2, 17, 0.8, 0.0, 97, '2023-1-3'),
(9, 3, 1, 11, 0.5, 1.5, 39, '2023-1-3');
INSERT INTO tags VALUES
(1, 1, 'tag_1');
The following query from the same tables with an empty tag list gives a table with null values from the inner join so I can use it with union all on itself - like I attempt to do with the query above - and separate the results easily. It is demonstrated in db<>fiddle, too.
-- The first row of the result displays calculations from the tagged restaurants.
-- In this case it has null values because tag list is empty.
-- The second row of the result displays calculations from all the restaurants without
-- considering any tags.
SELECT Total, Total - Drinks AS "Without drinks", Drinks
FROM (
SELECT
SUM (f.prepared_kgs)
FILTER (
WHERE f.days BETWEEN '2000-1-1' AND '2099-1-1'
AND f.restaurant_id = ANY (array[1, 2, 3])
) AS Total,
SUM (f.prepared_kgs)
FILTER (
WHERE (f.product_id = 10 OR f.product_id = 17)
AND f.days BETWEEN '2000-1-1' AND '2099-1-1'
AND f.restaurant_id = ANY (array[1, 2, 3])
) AS Drinks
FROM food f
INNER JOIN tags t
ON t.restaurant_id = f.restaurant_id
WHERE t.name = ANY (array[]::text[]) -- Empty tag list as an argument.
)
UNION ALL
-- The same query without the INNER JOIN and tag list argument.
SELECT Total, Total - Drinks AS "Without drinks", Drinks
FROM (
SELECT
SUM (f.prepared_kgs)
FILTER (
WHERE f.days BETWEEN '2000-1-1' AND '2099-1-1'
AND f.restaurant_id = ANY (array[1, 2, 3])
) AS Total,
SUM (f.prepared_kgs)
FILTER (
WHERE (f.product_id = 10 OR f.product_id = 17)
AND f.days BETWEEN '2000-1-1' AND '2099-1-1'
AND f.restaurant_id = ANY (array[1, 2, 3])
) AS Drinks
FROM food f
);
-- If tag list had elements, my program uses the first row.
-- Otherwise it uses the second row. Time series query returns
-- rows for each day but the logic I try to use is the same.
total Without drinks drinks
null null null
25.1 13.1 12.0
SELECT 2
tagstable has aUNIQUEconstraint on therestaurant_idcolumnLEFT JOIN tags t ON f.restaurant_id = t.restaurant_id AND t.name = ANY (array[]::text[])instead of anINNER JOINbut I'm not quite sure what exactly your desired result is. What do you mean by "result withNULLrows"? Normally you don't get any result rows from nothing (SUMis an exception to the rule), so you'll have to filter for at least something and I'm not sure in which columns you expect aNULLvalue then.UNIQUE (restaurant_id)means each restaurant can only appear once. If it can only appear once, it can't have multiple tags.UNIQUE (restaurant_id, name)means each restaurant can appear multiple times, but each occurence must have a different tag from the other occurences (can have multiple tags, but no tag more than once).tagstable and add atagcolumn torestaurants. If tags are restricted to a set of names, then remove the reference torestaurantsfrom tags and either add a reference totagstorestaurantsif each restaurant can have only one tag, or create arestaurants_tagsassociative (bridge) table.