Postgres 15

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 margins (
    id serial primary key,
    margins JSON,
  created_at TIMESTAMP NOT NULL,
  institution_uuid UUID NOT NULL,
  created_by VARCHAR
);

insert into margins (margins, created_at, institution_uuid, created_by) VALUES ('[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "CUSTOM"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]', '2023-12-12 15:38:40.642428', '51d8060e-5a31-4575-b56d-c5100e94d614', 'test-runner') RETURNING *;
insert into margins (margins, created_at, institution_uuid, created_by) VALUES ('[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "DEFAULT"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]', '2023-12-12 15:38:41.642428', '51d8060e-5a31-4575-b56d-c5100e94d614', 'test-runner') RETURNING *;
insert into margins (margins, created_at, institution_uuid, created_by) VALUES ('[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "DEFAULT"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]', '2023-12-12 15:38:42.642428', '51d8060e-5a31-4575-b56d-c5100e94d614', 'test-runner') RETURNING  *;

insert into margins (margins, created_at, institution_uuid, created_by) VALUES ('[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "CUSTOM"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]', '2023-12-12 15:38:40.642428', '7e6e50a3-d53b-4108-8337-b3dc05e0353a', 'test-runner') RETURNING  *;
insert into margins (margins, created_at, institution_uuid, created_by) VALUES ('[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "DEFAULT"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]', '2023-12-12 15:38:41.642428', '7e6e50a3-d53b-4108-8337-b3dc05e0353a', 'test-runner') RETURNING  *;
insert into margins (margins, created_at, institution_uuid, created_by) VALUES ('[{"model": "samsungS23","margin": "0.5","type": "DEFAULT"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "DEFAULT"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]', '2023-12-12 15:38:42.642428', '7e6e50a3-d53b-4108-8337-b3dc05e0353a', 'test-runner') RETURNING  *;

insert into margins (margins, created_at, institution_uuid, created_by) VALUES ('[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "CUSTOM"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]', '2023-12-12 15:38:40.642428', '9348682d-cf21-43dc-b456-76b51a7da83b', 'test-runner') RETURNING  *;
insert into margins (margins, created_at, institution_uuid, created_by) VALUES ('[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "DEFAULT"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]', '2023-12-12 15:38:41.642428', '9348682d-cf21-43dc-b456-76b51a7da83b', 'test-runner') RETURNING  *;
insert into margins (margins, created_at, institution_uuid, created_by) VALUES ('[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "CUSTOM"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]', '2023-12-12 15:38:42.642428', '9348682d-cf21-43dc-b456-76b51a7da83b', 'test-runner') RETURNING  *;

CREATE TABLE




id
margins
created_at
institution_uuid
created_by


1
[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "CUSTOM"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]
2023-12-12 15:38:40.642428
51d8060e-5a31-4575-b56d-c5100e94d614
test-runner



INSERT 0 1




id
margins
created_at
institution_uuid
created_by


2
[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "DEFAULT"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]
2023-12-12 15:38:41.642428
51d8060e-5a31-4575-b56d-c5100e94d614
test-runner



INSERT 0 1




id
margins
created_at
institution_uuid
created_by


3
[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "DEFAULT"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]
2023-12-12 15:38:42.642428
51d8060e-5a31-4575-b56d-c5100e94d614
test-runner



INSERT 0 1




id
margins
created_at
institution_uuid
created_by


4
[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "CUSTOM"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]
2023-12-12 15:38:40.642428
7e6e50a3-d53b-4108-8337-b3dc05e0353a
test-runner



INSERT 0 1




id
margins
created_at
institution_uuid
created_by


5
[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "DEFAULT"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]
2023-12-12 15:38:41.642428
7e6e50a3-d53b-4108-8337-b3dc05e0353a
test-runner



INSERT 0 1




id
margins
created_at
institution_uuid
created_by


6
[{"model": "samsungS23","margin": "0.5","type": "DEFAULT"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "DEFAULT"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]
2023-12-12 15:38:42.642428
7e6e50a3-d53b-4108-8337-b3dc05e0353a
test-runner



INSERT 0 1




id
margins
created_at
institution_uuid
created_by


7
[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "CUSTOM"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]
2023-12-12 15:38:40.642428
9348682d-cf21-43dc-b456-76b51a7da83b
test-runner



INSERT 0 1




id
margins
created_at
institution_uuid
created_by


8
[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "DEFAULT"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]
2023-12-12 15:38:41.642428
9348682d-cf21-43dc-b456-76b51a7da83b
test-runner



INSERT 0 1




id
margins
created_at
institution_uuid
created_by


9
[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "CUSTOM"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]
2023-12-12 15:38:42.642428
9348682d-cf21-43dc-b456-76b51a7da83b
test-runner



INSERT 0 1


create table margin_defaults (
  id serial primary key,
	model VARCHAR(255) NOT NULL,
	margin FLOAT NOT NULL,
	created_by VARCHAR(255) NOT NULL,
	created_at TIMESTAMP NOT NULL DEFAULT NOW(),
	updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

insert into margin_defaults (model, margin, created_by) VALUES ('samsungS23', 0.4, 'default-runner') RETURNING *;
insert into margin_defaults (model, margin, created_by) VALUES ('iphone', 0.7, 'default-runner') RETURNING *;

CREATE TABLE




id
model
margin
created_by
created_at
updated_at


1
samsungS23
0.4
default-runner
2023-12-17 11:34:27.996376
2023-12-17 11:34:27.996376



INSERT 0 1




id
model
margin
created_by
created_at
updated_at


2
iphone
0.7
default-runner
2023-12-17 11:34:27.996376
2023-12-17 11:34:27.996376



INSERT 0 1


CREATE OR REPLACE FUNCTION update_margins_default_values_function()
RETURNS TRIGGER AS $$
DECLARE r RECORD;
DECLARE tmp_position int;
BEGIN
		FOR r IN
				SELECT DISTINCT ON (b.institution_uuid) * FROM (SELECT DISTINCT ON (institution_uuid) * FROM margins ORDER BY institution_uuid, created_at DESC) as b WHERE b.margins::jsonb@>'[{"type":"DEFAULT"}]' ORDER BY institution_uuid
		LOOP
			SELECT position FROM r, jsonb_array_elements(margins) with ordinality arr(elem, position) INTO tmp_position WHERE elem->>'model'=NEW.model AND elem->>'type'='DEFAULT';
			IF found THEN
				INSERT INTO margins (institution_uuid, margins, created_by) VALUES (
					r.institution_uuid,
					jsonb_set(r.margins::jsonb, ('{' || tmp_position || ',margin}')::text[], NEW.margin)::json,
					NEW.created_by
				);
			END IF;
		END LOOP;
    RETURN 1;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE TRIGGER update_margins_default_values_trigger
	AFTER INSERT ON margin_defaults FOR EACH ROW EXECUTE PROCEDURE update_margins_default_values_function();

CREATE FUNCTION



CREATE TRIGGER


insert into margin_defaults (model, margin, created_by) VALUES ('samsungS23', 0.1, 'default-runner') RETURNING *;

ERROR:  relation "r" does not exist
LINE 1: SELECT position FROM r, jsonb_array_elements(margins) with o...
                             ^
QUERY:  SELECT position FROM r, jsonb_array_elements(margins) with ordinality arr(elem, position)                   WHERE elem->>'model'=NEW.model AND elem->>'type'='DEFAULT'
CONTEXT:  PL/pgSQL function update_margins_default_values_function() line 8 at SQL statement


SELECT DISTINCT ON (institution_uuid) * FROM margins ORDER BY institution_uuid, created_at DESC


id
margins
created_at
institution_uuid
created_by


3
[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "DEFAULT"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]
2023-12-12 15:38:42.642428
51d8060e-5a31-4575-b56d-c5100e94d614
test-runner

6
[{"model": "samsungS23","margin": "0.5","type": "DEFAULT"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "DEFAULT"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]
2023-12-12 15:38:42.642428
7e6e50a3-d53b-4108-8337-b3dc05e0353a
test-runner

9
[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "CUSTOM"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]
2023-12-12 15:38:42.642428
9348682d-cf21-43dc-b456-76b51a7da83b
test-runner



SELECT 3
id	margins	created_at	institution_uuid	created_by
3	[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "DEFAULT"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]	2023-12-12 15:38:42.642428	51d8060e-5a31-4575-b56d-c5100e94d614	test-runner
6	[{"model": "samsungS23","margin": "0.5","type": "DEFAULT"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "DEFAULT"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]	2023-12-12 15:38:42.642428	7e6e50a3-d53b-4108-8337-b3dc05e0353a	test-runner
9	[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","serial_number": "12333","margin": "0.2","type": "CUSTOM"},{"model": "pixel","serial_number": "12333","margin": "0.2","type": "CUSTOM"}]	2023-12-12 15:38:42.642428	9348682d-cf21-43dc-b456-76b51a7da83b	test-runner