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 TYPE status AS ENUM ('Active', 'Failed');
DROP TABLE IF EXISTS Device_Status;
CREATE TABLE Device_Status
(
ID integer,
Status status,
temptime text
);
INSERT INTO Device_Status VALUES (1,'Active','2023-01-13 10.00.01.528');
INSERT INTO Device_Status VALUES (2,'Active','2023-01-13 10.00.01.529');
INSERT INTO Device_Status VALUES (1,'Failed','2023-01-13 10.00.01.530');
INSERT INTO Device_Status VALUES (3,'Active','2023-01-13 10.00.01.531');
INSERT INTO Device_Status VALUES (1,'Failed','2023-01-13 10.00.01.532');
INSERT INTO Device_Status VALUES (1,'Active','2023-01-13 10.00.01.533');
INSERT INTO Device_Status VALUES (3,'Active','2023-01-13 10.00.01.534');
INSERT INTO Device_Status VALUES (1,'Failed','2023-01-13 10.00.01.535');
INSERT INTO Device_Status VALUES (4,'Failed','2023-01-13 10.00.01.536');
INSERT INTO Device_Status VALUES (1,'Active','2023-01-13 10.00.01.537');
insert into device_status values (1,'Failed','2023-01-13 10:00:01.538'),
(1,'Failed','2023-01-13 10:00:01.539'),
(1,'Failed','2023-01-13 10:00:01.540'),
(1,'Failed','2023-01-13 10:00:01.541'),
(1,'Failed','2023-01-13 10:00:01.542'),
(1,'Failed','2023-01-13 10:00:01.543'),
(1,'Failed','2023-01-13 10:00:01.544'),
(1,'Failed','2023-01-13 10:00:01.545'),
(1,'Failed','2023-01-13 10:00:01.546'),
(1,'Failed','2023-01-13 10:00:01.547');
ALTER TABLE Device_Status ADD timestamp TIMESTAMP;
UPDATE Device_Status set timestamp =
to_timestamp(temptime,'YYYY-MM-DD HH:MI:SS.MS');
ALTER TABLE Device_Status DROP COLUMN temptime;
WITH Device_Status_With_Change_Column AS
(
WITH flag AS
(
CREATE TYPE
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 10
ALTER TABLE
UPDATE 20
ALTER TABLE
| id | status | timestamp | last_status | last_id | change_flag | change |
|---|---|---|---|---|---|---|
| 1 | Active | 2023-01-13 10:00:01.528 | null | 0 | 0 | 0 |
| 1 | Failed | 2023-01-13 10:00:01.53 | Active | 1 | 1 | 1 |
| 1 | Failed | 2023-01-13 10:00:01.532 | Failed | 1 | 0 | 1 |
| 1 | Active | 2023-01-13 10:00:01.533 | Failed | 1 | 1 | 2 |
| 1 | Failed | 2023-01-13 10:00:01.535 | Active | 1 | 1 | 3 |
| 1 | Active | 2023-01-13 10:00:01.537 | Failed | 1 | 1 | 4 |
| 1 | Failed | 2023-01-13 10:00:01.538 | Active | 1 | 1 | 5 |
| 1 | Failed | 2023-01-13 10:00:01.539 | Failed | 1 | 0 | 5 |
| 1 | Failed | 2023-01-13 10:00:01.54 | Failed | 1 | 0 | 5 |
| 1 | Failed | 2023-01-13 10:00:01.541 | Failed | 1 | 0 | 5 |
| 1 | Failed | 2023-01-13 10:00:01.542 | Failed | 1 | 0 | 5 |
| 1 | Failed | 2023-01-13 10:00:01.543 | Failed | 1 | 0 | 5 |
| 1 | Failed | 2023-01-13 10:00:01.544 | Failed | 1 | 0 | 5 |
| 1 | Failed | 2023-01-13 10:00:01.545 | Failed | 1 | 0 | 5 |
| 1 | Failed | 2023-01-13 10:00:01.546 | Failed | 1 | 0 | 5 |
| 1 | Failed | 2023-01-13 10:00:01.547 | Failed | 1 | 0 | 5 |
| 2 | Active | 2023-01-13 10:00:01.529 | null | 0 | 0 | 0 |
| 3 | Active | 2023-01-13 10:00:01.531 | null | 0 | 0 | 0 |
| 3 | Active | 2023-01-13 10:00:01.534 | Active | 3 | 0 | 0 |
| 4 | Failed | 2023-01-13 10:00:01.536 | null | 0 | 0 | 0 |
SELECT 20