Postgres 11

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 IF NOT EXISTS tracking (
    id SERIAL NOT NULL PRIMARY KEY,
    number_of_devices INT NOT NULL,
    created TIMESTAMP DEFAULT now(),
    modified TIMESTAMP DEFAULT now(),
    deleted TIMESTAMP
);

CREATE TABLE IF NOT EXISTS condition (
    id SERIAL NOT NULL PRIMARY KEY,
    tracking_id BIGINT NOT NULL,
    name VARCHAR(100),
    type VARCHAR(50) NOT NULL,
    script VARCHAR(10000) NOT NULL,
    event_count INT DEFAULT 0,
    created TIMESTAMP DEFAULT now(),
    modified TIMESTAMP DEFAULT now(),
    deleted TIMESTAMP,
    FOREIGN KEY ("tracking_id") REFERENCES tracking("id")
);

CREATE TABLE IF NOT EXISTS pairing (
    id SERIAL NOT NULL PRIMARY KEY,
    tracking_id BIGINT NOT NULL,
    description VARCHAR(200),
    device_id BIGINT NOT NULL,
    serial VARCHAR(50),
    brand VARCHAR(100),
    created TIMESTAMP DEFAULT now(),
    modified TIMESTAMP DEFAULT now(),
    deleted TIMESTAMP,
    FOREIGN KEY ("tracking_id") REFERENCES tracking("id")
);

CREATE TABLE IF NOT EXISTS data (
    id SERIAL NOT NULL PRIMARY KEY,
    organization_id BIGINT NOT NULL,
    pairing_id BIGINT NOT NULL,
    server_time TIMESTAMP,
    "time" TIMESTAMP,
    battery INT,
    "interval" INT,
    case_opened BOOLEAN,
    light BOOLEAN,
    shock BOOLEAN,
    "drop" BOOLEAN,
    tilt BOOLEAN,
    humidity INT,
    button_press BOOLEAN,
    temperature INT,
    location_type VARCHAR(10),
    latitude FLOAT8,
    longitude FLOAT8,
    device_id BIGINT,
    device_brand VARCHAR(100),
    device_model VARCHAR(100),
    device_serial VARCHAR(50),
    device_tracking_id BIGINT,
    created TIMESTAMP DEFAULT now(),
    modified TIMESTAMP DEFAULT now(),
    deleted TIMESTAMP,
    FOREIGN KEY ("device_tracking_id") REFERENCES tracking("id"),
    FOREIGN KEY ("pairing_id") REFERENCES pairing("id")
);

CREATE TABLE IF NOT EXISTS event (
    id SERIAL NOT NULL PRIMARY KEY,
    data_id BIGINT NOT NULL,
    tracking_id BIGINT NOT NULL,
    condition_id BIGINT NOT NULL,
    created TIMESTAMP DEFAULT now(),
    modified TIMESTAMP DEFAULT now(),
    deleted TIMESTAMP,
    FOREIGN KEY ("tracking_id") REFERENCES tracking("id"),
    FOREIGN KEY ("data_id") REFERENCES data("id"),
    FOREIGN KEY ("condition_id") REFERENCES condition("id")
);

create or replace function json_object_nullif(
    _data json
)
returns json
as $$
    select nullif(json_strip_nulls(_data)::text, '{}')::json
$$ language sql;

create or replace function json_array_nullif(
    _data json
)
returns json
as $$
    select nullif(_data::text, '[null]')::json
$$ language sql;

CREATE OR REPLACE VIEW data_list AS
	select
	    organization_id,
	    pairing_id,
            json_object_nullif(json_build_object(
              'id', d.id,
              'organization_id', d.organization_id,
              'pairing_id', d.pairing_id,
              'server_time', to_char(d.server_time at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
              'time', to_char(d."time" at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
              'battery', d.battery,
              'interval', d."interval",
              'case_opened', d.case_opened,
              'light', d.light,
              'shock', d.shock,
              'drop', d."drop",
              'humidity', d.humidity,
              'button_press', d.button_press,
              'temperature', d.temperature,
              'location', json_object_nullif(json_build_object(
                  'type', d.location_type,
                  'latitude', d.latitude,
                  'longitude', d.longitude
              )),
              'device', json_object_nullif(json_build_object(
                  'id', d.device_id,
                  'brand', d.device_brand,
                  'model', d.device_model,
                  'serial', d.device_serial,
                  'tracking_id', d.device_tracking_id
              )),
              'events', json_array_nullif(json_agg(
                   json_object_nullif(json_build_object(
                       'id', e.id,
                       'created', to_char(e.created::timestamptz at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
                       'condition_id', e.condition_id
                   ))
               ))
            )) as gson
        from "data" as d
        left JOIN "event" AS e ON e.data_id = d.id
	    group by d.id
	    ORDER BY d.id desc;
        
        
INSERT INTO tracking (number_of_devices) VALUES (1);
INSERT INTO pairing (tracking_id, description, device_id, "serial", brand) VALUES (1, 'something', 1, '123456789', 'comany');
INSERT INTO data (organization_id, pairing_id, device_tracking_id) VALUES (1, 1, 1);
INSERT INTO data (organization_id, pairing_id, device_tracking_id) VALUES (1, 1, 1);
INSERT INTO condition (tracking_id, "type", script) VALUES (1, 'something', 'something'); 
INSERT INTO "event" (data_id, tracking_id, condition_id) VALUES (2, 1, 1);

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

organization_id	pairing_id	gson
1	1	{"id":2,"organization_id":1,"pairing_id":1,"device":{"tracking_id":1},"events":[{"id":1,"created":"2022-05-23T10:26:44Z","condition_id":1}]}
1	1	{"id":1,"organization_id":1,"pairing_id":1,"device":{"tracking_id":1}}