Postgres 12

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 sensor_data(
SerialNr serial,
nodeaddress macaddr primary key,
connection_status varchar,
temperature int,
humidity int,
lux int,
battery int, 
conn_lastupdated timestamp NOT NULL DEFAULT NOW(),
temp_lastupdated timestamp NOT NULL DEFAULT NOW(),
hum_lastupdated timestamp NOT NULL DEFAULT NOW(),
lux_lastupdated timestamp NOT NULL DEFAULT NOW(),
batt_lastupdated timestamp NOT NULL DEFAULT NOW()
);
INSERT INTO sensor_data (nodeaddress) VALUES('00:0a:95:9d:68:17');
INSERT INTO sensor_data (nodeaddress) VALUES('00:0a:95:9d:68:18');
INSERT INTO sensor_data (nodeaddress) VALUES('00:0a:95:9d:68:19');
INSERT INTO sensor_data (nodeaddress) VALUES('00:0a:95:9d:68:20');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT * FROM sensor_data;


serialnr
nodeaddress
connection_status
temperature
humidity
lux
battery
conn_lastupdated
temp_lastupdated
hum_lastupdated
lux_lastupdated
batt_lastupdated


1
00:0a:95:9d:68:17
null
null
null
null
null
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473

2
00:0a:95:9d:68:18
null
null
null
null
null
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473

3
00:0a:95:9d:68:19
null
null
null
null
null
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473

4
00:0a:95:9d:68:20
null
null
null
null
null
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473


create procedure sensor_data_db (payload jsonb)
language sql
as $$
    with j as (
       select payload as p
    ), cte as (
        select 
            (p->>'nodeaddress')::macaddr as nodeaddress,
            case when p ? 'temperature' then (p->>'temperature')::int else f.temperature end as temperature,
            case when p ? 'humidity' then (p->>'humidity')::int else f.humidity end as humidity,
            case when p ? 'lux' then (p->>'lux')::int else f.lux end as lux,
			case when p ? 'battery' then (p->>'battery')::int else f.battery end as battery,
			case when p ? 'connection_status' then (p->>'connection_status')::varchar else f.connection_status end as connection_status,
			
            case when p ? 'temperature' then now()::timestamp else f.temp_lastupdated end as temp_lastupdated,
            case when p ? 'humidity' then now()::timestamp else f.hum_lastupdated end as hum_lastupdated,
            case when p ? 'lux' then now()::timestamp else f.lux_lastupdated end as lux_lastupdated,
			case when p ? 'battery' then now()::timestamp else f.batt_lastupdated end as batt_lastupdated,
			case when p ? 'connection_status' then now()::timestamp else f.conn_lastupdated end as conn_lastupdated
			
        from sensor_data f
        join j on (j.p->>'nodeaddress')::macaddr = f.nodeaddress 
    )
    update sensor_data as f set
        temperature = c.temperature,
        humidity = c.humidity,
        lux = c.lux,
		battery = c.battery,
		connection_status = c.connection_status,
		temp_lastupdated = c.temp_lastupdated,
        hum_lastupdated = c.hum_lastupdated,
        lux_lastupdated = c.lux_lastupdated,
		batt_lastupdated = c.batt_lastupdated,
		conn_lastupdated = c.conn_lastupdated
    
	from cte as c
    where c.nodeaddress = f.nodeaddress;
$$
SELECT * FROM sensor_data;


serialnr
nodeaddress
connection_status
temperature
humidity
lux
battery
conn_lastupdated
temp_lastupdated
hum_lastupdated
lux_lastupdated
batt_lastupdated


1
00:0a:95:9d:68:17
null
null
null
null
null
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473

2
00:0a:95:9d:68:18
null
null
null
null
null
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473

3
00:0a:95:9d:68:19
null
null
null
null
null
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473

4
00:0a:95:9d:68:20
null
null
null
null
null
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473


-- to make the timestamp change more obvious
select pg_sleep(10);


pg_sleep





call sensor_data_db('{"temperature":20,"nodeaddress":"00:0a:95:9d:68:17"}') ;
SELECT * FROM sensor_data;


serialnr
nodeaddress
connection_status
temperature
humidity
lux
battery
conn_lastupdated
temp_lastupdated
hum_lastupdated
lux_lastupdated
batt_lastupdated


2
00:0a:95:9d:68:18
null
null
null
null
null
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473

3
00:0a:95:9d:68:19
null
null
null
null
null
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473

4
00:0a:95:9d:68:20
null
null
null
null
null
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473

1
00:0a:95:9d:68:17
null
20
null
null
null
2020-03-18 13:19:49.72473
2020-03-18 13:19:59.74196
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473
2020-03-18 13:19:49.72473

serialnr	nodeaddress	connection_status	temperature	humidity	lux	battery	conn_lastupdated	temp_lastupdated	hum_lastupdated	lux_lastupdated	batt_lastupdated
1	00:0a:95:9d:68:17	null	null	null	null	null	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473
2	00:0a:95:9d:68:18	null	null	null	null	null	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473
3	00:0a:95:9d:68:19	null	null	null	null	null	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473
4	00:0a:95:9d:68:20	null	null	null	null	null	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473	2020-03-18 13:19:49.72473