add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE inventory (
inventory_id serial PRIMARY KEY,
arrive_date date NOT NULL,
arrive_location character varying NOT NULL,
thing_type integer NOT NULL,
quantity integer NOT NULL
);

CREATE TABLE preprocess_things (
preprocess_id serial PRIMARY KEY,
arrive_date date NOT NULL,
arrive_location character varying NOT NULL,
data jsonb NOT NULL,
CONSTRAINT preprocess_things_arrive_date_arrive_location_bo_key UNIQUE (arrive_date, arrive_location)
);

INSERT INTO inventory (arrive_date, arrive_location, thing_type, quantity) VALUES
('2018-05-30 00:00:00-00', 'location_00', 3, 2)
, ('2018-05-31 00:00:00-00', 'location_00', 3, 8)
, ('2018-05-31 00:00:00-00', 'location_00', 4, 7)
;
CREATE TABLE

CREATE TABLE

INSERT 0 3
-- derived table in CTE "result_query"
SELECT arrive_date, arrive_location
, jsonb_object_agg(thing_type, total_things)
FROM (
SELECT date_trunc('day', arrive_date) AS arrive_date
-- additional column "arrive_date" had to go
, arrive_location
, thing_type
, sum(quantity) AS total_things
FROM inventory
GROUP BY date_trunc('day', arrive_date), arrive_location, thing_type
) sub
GROUP BY arrive_date, arrive_location;
arrive_date arrive_location jsonb_object_agg
2018-05-31 00:00:00+01 location_00 {"3": 8, "4": 7}
2018-05-30 00:00:00+01 location_00 {"3": 2} 
SELECT 2
WITH result_query AS (
SELECT arrive_date, arrive_location
, json_object_agg(thing_type, total_things)
FROM (
SELECT date_trunc('day', arrive_date) AS arrive_date
-- additional column "arrive_date" had to go
, arrive_location
, thing_type
, sum(quantity) AS total_things
FROM inventory
GROUP BY date_trunc('day', arrive_date), arrive_location, thing_type
) sub
GROUP BY arrive_date, arrive_location
)
INSERT INTO preprocess_things AS p (arrive_date, arrive_location, data)
TABLE result_query
ON CONFLICT (arrive_date, arrive_location) DO
UPDATE
SET data = p.data || EXCLUDED.data
WHERE p.data IS DISTINCT FROM p.data || EXCLUDED.data; -- exclude empty updates
INSERT 0 2
TABLE preprocess_things;
preprocess_id arrive_date arrive_location data
1 2018-05-31 location_00 {"3": 8, "4": 7}
2 2018-05-30 location_00 {"3": 2} 
SELECT 2
-- add things
INSERT INTO inventory (arrive_date, arrive_location, thing_type, quantity) VALUES
('2018-05-31 00:00:00-00', 'location_00', 4, 3)
;
INSERT 0 1
-- run again
WITH result_query AS (
SELECT arrive_date, arrive_location
, jsonb_object_agg(thing_type, total_things)
FROM (
SELECT date_trunc('day', arrive_date) AS arrive_date
-- additional column "arrive_date" had to go
, arrive_location
, thing_type
, sum(quantity) AS total_things
FROM inventory
GROUP BY date_trunc('day', arrive_date), arrive_location, thing_type
) sub
GROUP BY arrive_date, arrive_location
)
INSERT INTO preprocess_things AS p (arrive_date, arrive_location, data)
TABLE result_query
ON CONFLICT (arrive_date, arrive_location) DO
UPDATE
SET data = p.data || EXCLUDED.data
WHERE p.data IS DISTINCT FROM p.data || EXCLUDED.data; -- exclude empty updates
INSERT 0 1
TABLE preprocess_things; -- thing "4" is overwritten
preprocess_id arrive_date arrive_location data
2 2018-05-30 location_00 {"3": 2}
1 2018-05-31 location_00 {"3": 8, "4": 10} 
SELECT 2