Postgres 14

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

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}

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}